﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using Antu.Core.DataAccess;

namespace EluZSProvider
{
    public sealed class EluZSSqlDataProvider : EluZSDataProvider
    {
        internal EluZSSqlDataProvider()
        {
        }
        protected string ZSConnectionString
        {
            get
            {
                return (base.ProviderProperties["ZSConnectionString"].ToString());
            }
        }
        protected string Neto2ConnectionString
        {
            get
            {
                return System.Configuration.ConfigurationManager.AppSettings["Neto2ConnectionString"].ToString();
            }
        }
        protected IDataAccess Neto2ida 
        {
            get
            {
                return GetDicDatabase("Neto2ConnectionString", Neto2ConnectionString, false);
            }
        }

        /// <summary>
        /// 外网数据库连接
        /// </summary>
        protected IDataAccess Outida
        {
            get
            {
                //return DataFactory.GetInstance(false, DatabaseType.Oracle, ZSConnectionString);
                return GetDicDatabase("AntuZSConnectionString", ZSConnectionString, false);
            }
        }

        #region 实现动态重用联接
        static private Dictionary<string, IDataAccess> _dicDatabase = new Dictionary<string, IDataAccess>(StringComparer.InvariantCultureIgnoreCase);
        /// <summary>
        /// 实现动态重用联接
        /// </summary>
        /// <param name="name">联接名</param>
        /// <param name="value">联接值</param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static IDataAccess GetDicDatabase(string name, string value, bool transaction)
        {
            IDataAccess dbTemp = null;
            lock (_dicDatabase)
            {
                if (_dicDatabase.ContainsKey(name))
                {
                    dbTemp = _dicDatabase[name];
                }
                else
                {
                    dbTemp = DataFactory.GetInstance(transaction, DatabaseType.Oracle, value);
                    _dicDatabase.Add(name, dbTemp);
                }
            }
            return dbTemp;
        }
        #endregion

        public override string GetConnectionString()
        {
            return (base.ProviderProperties["ZSConnectionString"].ToString());
        }
        public override User GetUser(string userid)
        {
            User user = new User();
            string sql = "select * from zs_user where id='" + userid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return user = DataHelper.DataTableToTOne<User>(dt);
        }



        public override User GetUser(string username, string pwd)
        {
            User user = new User();
            string sql = "select * from zs_user where LOGINID='" + username + "' and pwd='" + pwd + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return user = DataHelper.DataTableToTOne<User>(dt);
        }

        public override List<User> GetAllUserList()
        {
            List<User> userlist = new List<User>();
            string sql = "select * from zs_user ";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return userlist = DataHelper.DataTableToT<User>(dt);
        }

        public override List<User> GetAllEffectiveUserList()
        {
            List<User> userlist = new List<User>();
            string sql = "select * from zs_user where status=1 ";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return userlist = DataHelper.DataTableToT<User>(dt);
        }

        public override List<User> GetUserList(string roleid)
        {
            List<User> userlist = new List<User>();
            string sql = @"select * from zs_user where id in(select distinct userid from zs_user_in_role where ROLEID='" + roleid + "')";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return userlist = DataHelper.DataTableToT<User>(dt);
        }

        public override Role GetRole(string roleid)
        {
            Role role = new Role();
            string sql = "select * from zs_role where id='" + roleid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return role = DataHelper.DataTableToTOne<Role>(dt);
        }

        public override List<Role> GetRoleList(string userid)
        {
            List<Role> rolelist = new List<Role>();
            string sql = "select * from zs_role where id in(select roleid from zs_user_in_role where userid='" + userid + "')";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            for (int temp = 0; temp < dt.Rows.Count; temp++)
            {
                Role role = new Role();
                role.Id = dt.Rows[temp]["ID"].ToString();
                role.Rolename = dt.Rows[temp]["ROLENAME"].ToString();
                role.Memo = dt.Rows[temp]["MEMO"].ToString();
                role.Addtime = (string.IsNullOrEmpty(dt.Rows[temp]["ADDTIME"].ToString()) ? null : (DateTime?)Convert.ToDateTime(dt.Rows[temp]["ADDTIME"].ToString()));

                rolelist.Add(role);
            }
            return rolelist;
        }

        public override bool SaveUser(string strid, User user)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(strid))
            {
                sql = DataHelper.AddT<User>(user, "ZS_User");
            }
            else
            {
                sql = DataHelper.UpdateT<User>(user, "ZS_User", new string[] { "id" });
            }
            return Outida.RunSql(sql) > 0 ? true : false;
        }

        public override bool SaveRizhi(Rizhi rizhi)
        {
            string sql = DataHelper.AddT<Rizhi>(rizhi, "ZS_RIZHI");
            return Outida.RunSql(sql) > 0 ? true : false;
        }

        public override bool DelUser(string userid)
        {
            return false;
        }

        public override bool DelRizhi(string rizhiid)
        {
            return false;
        }
        public override List<Menu> GetMenu(string mid)
        {
            List<Menu> menulist = new List<Menu>();
            string sql = "select * from zs_menu where id ='" + mid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            menulist = DataHelper.DataTableToT<Menu>(dt);
            return menulist;
        }
        public override List<Menu> GetMenuTitle()
        {
            List<Menu> menulist = new List<Menu>();
            string sql = "select * from zs_menu Where Parent_Id  Is Null  and  IS_DELETE=0  order By order_id Asc ";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            menulist = DataHelper.DataTableToT<Menu>(dt);
            return menulist;
        }
        public override List<Menu> GetMenuContent(string mid)
        {
            List<Menu> menulist = new List<Menu>();
            string sql = "select * from zs_menu Where Parent_Id  Is not Null  and  IS_DELETE=0 and Parent_Id='" + mid + "'  order By order_id Asc ";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            menulist = DataHelper.DataTableToT<Menu>(dt);
            return menulist;
        }
        public override DataTable GetMenuTitleByUserID(string userid)
        {
            string sql = @" Select t.*,t.rowid From zs_Menu t Where  IS_DELETE=0 and  Id In(
            select distinct * from  
            (
                   Select distinct Parent_Id From zs_Menu t Where   IS_DELETE=0 and 
            Id In(Select distinct menuid From zs_Menu_In_Role a Where a.roleid In(Select roleid From zs_USER_IN_ROLE
             Where userid='" + userid + @"'))
             union all
            SELECT DISTINCT Parent_Id From zs_Menu t WHERE ID IN 
            (select MENUID from zs_menu_in_user WHERE USERID='" + userid + @"')
            )
            ) order By order_id Asc";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetMenuByUserID(string userid, string mid)
        {
            string sql = @" Select t.*  From zs_Menu t Where   Id In(
            select distinct * from  
            (
                   Select distinct Parent_Id From zs_Menu t Where  IS_DELETE=0 and 
            Id In(Select distinct menuid From zs_Menu_In_Role a Where a.roleid In(Select roleid From zs_USER_IN_ROLE
             Where userid='" + userid + @"'))
             union all
            SELECT DISTINCT Parent_Id From zs_Menu t WHERE   IS_DELETE=0 and  ID IN 
            (select MENUID from zs_menu_in_user WHERE USERID='" + userid + @"')
            )
            ) and id='" + mid + "'  and IS_DELETE=0  order By order_id Asc";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetMenuContentByUserID(string userid, string mid)
        {
            string sql = @"Select t.*,t.rowid From zs_Menu t Where   IS_DELETE=0 and  Parent_Id='" + mid + @"'
                            And Id In(
                select  distinct * from
                (
                Select  menuid From zs_Menu_In_Role a Where a.roleid In(Select roleid From zs_USER_IN_ROLE Where userid
                           ='" + userid + @"')
                union all
                Select  menuid From zs_menu_in_user a Where a.USERID 
                            In('" + userid + @"')
                )) order By order_id Asc";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override Menu GetMenuOne(string strid)
        {
            Menu menu = new Menu();
            string sql = "select * from zs_menu where id='" + strid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return menu = DataHelper.DataTableToTOne<Menu>(dt);
        }
        public override Menu GetMenuByRole(string roleid)
        {
            Menu menu = new Menu();
            string sql = "select * from zs_menu where id in (select menuid from zs_menu_in_role where roleid ='" + roleid + "')";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return menu = DataHelper.DataTableToTOne<Menu>(dt);
        }


        public override List<Company> GetCompanyList1(string id, string dwname)
        {
            List<Company> companylist = new List<Company>();
            string strWhere = string.Empty;
            string sqlStr = "Select 单位名称 From zs_company Where Id In( Select comid From ZS_PGBMJL Where pnid = '" + id + "')";
            if (!string.IsNullOrEmpty(dwname))
            {
                strWhere = string.Format(" and 单位名称 like '%{0}%' ", dwname);
            }
            DataTable dt = null;
            if (!string.IsNullOrEmpty(strWhere))
            {
                sqlStr += strWhere;
            }
            Outida.RunSql(sqlStr, out dt);
            companylist = DataHelper.DataTableToT<Company>(dt);
            return companylist;
        }

        public override List<Company> GetCompanyList(string dwname, string dwtype)
        {
            List<Company> companylist = new List<Company>();
            string sqlStr = string.Format("select ID,单位名称 from zs_company where 单位类别='{0}' order by 单位名称 ", dwtype);
            string strWhere = string.Empty;
            if (!string.IsNullOrEmpty(dwname))
            {
                strWhere = string.Format(" and 单位名称 like '%{0}%' ", dwname);
            }
            if (dwtype == "评估机构")
            {
                sqlStr = string.Format(@"select ID,单位名称,信用等级 from zs_company where 单位类别='评估机构' and 入围年度='{0}'", System.DateTime.Now.Year.ToString());
            }
            if (dwtype == "征收实施单位")
            {
                sqlStr = string.Format("select ID,单位名称,所属区属 from zs_company where 单位类别='{0}' ", dwtype);
            }
            if (!string.IsNullOrEmpty(strWhere))
            {
                sqlStr += strWhere;
            }
            DataTable dt = null;
            Outida.RunSql(sqlStr, out dt);
            companylist = DataHelper.DataTableToT<Company>(dt);
            return companylist;
        }
        public override Company GetCompany(string userid)
        {
            Company company = new Company();
            string sql = "select * from zs_company where id in (select cid from zs_user_company where userid ='" + userid + "')";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return company = DataHelper.DataTableToTOne<Company>(dt);
        }

        public override Company GetCompanyByRole(string roleid)
        {
            Company company = new Company();
            string sql = "select * from zs_company where id in (select cid from zs_user_company where userid in (select userid from zs_user_in_role where roleid='" + roleid + "'))";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return company = DataHelper.DataTableToTOne<Company>(dt);
        }

        public override List<Params> GetParams(string name)
        {
            List<Params> menulist = new List<Params>();
            string sql = "select keyname,keyvalue from zs_params where name ='" + name + "' order by keyvalue";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return menulist = DataHelper.DataTableToT<Params>(dt);
        }
        public override DataTable GetParamYB(string name, string strwhere)
        {
            string sql = @"select * from zs_params where name ='" + name + "'";
            if (!string.IsNullOrEmpty(strwhere)) { sql += strwhere; }
            sql += " order by keyvalue";

            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override string GetParamsOne(string name, string keyvalue)
        {
            string strkeyname = string.Empty;
            string sql = "select t.keyname from zs_params t where t.name = '" + name + "' and t.keyvalue = '" + keyvalue + "' ";
            strkeyname = Outida.GetValue(sql);
            return strkeyname;
        }
        public override List<Project> GetProject(string sqlwhere)
        {
            List<Project> menulist = new List<Project>();
            string sql = "select * from zs_project where 1=1";
            if (!string.IsNullOrEmpty(sqlwhere)) { sql += sqlwhere; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return menulist = DataHelper.DataTableToT<Project>(dt);
        }
        public override Project GetProjectXX(string id)
        {
            Project project = new Project();
            string sql = @"select * from zs_project where id ='" + id + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return project = DataHelper.DataTableToTOne<Project>(dt);
        }
        public override bool GetProjectZT(string tablename, string columnname, string pid, string tt)
        {
            string sqlStr = string.Format("update {0} set {1}='{2}',CAN_SYNC=1 where id='{3}'", tablename, columnname, tt, pid);
            return Outida.RunSql(sqlStr) > 0 ? true : false;
        }
        public override int GetProjectFJNo(string pid, string itemname, string usetype)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(itemname))
            {
                sql = @"select count(distinct (z2.itemname)) 文件夹数 from zs_file z2, zs_project z1
where z1.id = z2.itemid and z2.itemname in ('项目批准文件','规划许可文件') and z2.use_type='" + usetype + "' and z1.id = '" + pid + "'";
            }
            else
            {
                sql = @"select count(distinct (z2.itemname)) 文件夹数 from zs_file z2, zs_project z1
where z1.id = z2.itemid and z2.use_type='" + usetype + "' and z2.itemname ='" + itemname + "' and z1.id = '" + pid + "'";
            }
            return Convert.ToInt32(Outida.GetValue(sql).ToString());
        }
        public override int GetProjectFJAllNo(string pid, string usetype)
        {
            string sql = @"select count(distinct (z2.itemname)) 文件夹数 from zs_file z2, zs_project z1
where z1.id = z2.itemid and z2.use_type='" + usetype + "' and z1.id = '" + pid + "'";
            return Convert.ToInt32(Outida.GetValue(sql).ToString());
        }
        public override StringBuilder GetProFJName(string pid, string usetype)
        {
            StringBuilder mybuilder = new StringBuilder();
            string sql = @"select distinct (z2.itemname) itemname 
from zs_file z2, zs_project z1
where z1.id = z2.itemid and z2.use_type='" + usetype + "' and z1.id = '" + pid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string ttemp = dt.Rows[i]["itemname"].ToString();
                    if (mybuilder.Length > 0)
                    {
                        mybuilder.Append("，");
                    }
                    mybuilder.Append(ttemp);
                }
            }
            return mybuilder;
        }

        public override string GetBCFAID(string ifafter, string pid)
        {
            string sql = @"select id from zs_bcfaqs where pid ='" + pid + "' and 补偿实施='" + ifafter + "'";
            string bcfaid = Outida.GetValue(sql);
            return bcfaid;
        }
        public override List<File> GetFile(string itemid, string usetype, string ITEMNAME)
        {
            List<File> filelist = new List<File>();
            string sql = string.Empty;
            if (!string.IsNullOrEmpty(usetype))
            {
                sql = "select distinct(itemname) itemname from ZS_FILE where use_type='" + usetype + "' and ITEMID='" + itemid + "'";
            }
            else if (!string.IsNullOrEmpty(ITEMNAME))
            {
                sql = "select * from ZS_FILE where ITEMNAME='" + ITEMNAME + "' and ITEMID='" + itemid + "'";
            }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return filelist = DataHelper.DataTableToT<File>(dt);
        }
        public override List<File> GetAllFile(string itemid, string usetype, string ITEMNAME)
        {
            List<File> filelist = new List<File>();
            string sql = "select * from ZS_FILE where 1=1 ";
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(itemid)) { sqlwhere += " and ITEMID='" + itemid + "' "; }
            if (!string.IsNullOrEmpty(usetype)) { sqlwhere += " and use_type='" + usetype + "' "; }
            if (!string.IsNullOrEmpty(ITEMNAME)) { sqlwhere += " and ITEMNAME='" + ITEMNAME + "' "; }
            if (usetype == "102")
            {
                string strpersonid = Outida.GetValue("Select PERSONID From zs_hbhxy Where id='" + itemid + "'");
                if (!string.IsNullOrEmpty(strpersonid))
                {
                    sqlwhere = " And (use_type='102' Or use_type='99') And (ITEMID='" + itemid + "' Or ITEMID='" + strpersonid + "')";
                }
            }
            else if (usetype == "101")
            {
                string strpersonid = Outida.GetValue("Select zspid From zs_proloupan Where Id In (Select fid From zs_cqzhxy Where Id='" + itemid + "')");
                if (!string.IsNullOrEmpty(strpersonid))
                {
                    sqlwhere = " And (use_type='101' Or use_type='99') And (ITEMID='" + itemid + "' Or ITEMID='" + strpersonid + "')";
                }
            }

            sql += sqlwhere; 
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return filelist = DataHelper.DataTableToT<File>(dt);
        }
        public override File GetFileOne(string strid)
        {
            File file = new File();
            string sql = @"select * from zs_file where id ='" + strid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return file = DataHelper.DataTableToTOne<File>(dt);
        }
        public override DataTable GetOneFile(string strid)
        {
            string sql = @"select * from zs_file where id ='" + strid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override string GetFJZT(string tablename, string columname, string id)
        {
            string tt = string.Empty;
            string sql = @"select " + columname + " from " + tablename + " where id='" + id + "'";
            return tt = Outida.GetValue(sql);
        }
        public override void UpdateFileContent(string tablename, string id, byte[] FileContent, int Length, string itemid, string filename, string fileext, string itemname, string usetype)
        {
            string sql = string.Empty;
            string strid = GetFileId(itemid);
            if (!string.IsNullOrEmpty(strid))
            {
                if (usetype == "0" && itemname == "用户签名")
                {
                    id = strid;
                }
            }
            else
            {
                sql = @"insert into " + tablename + " (id,name,suffix,ITEMID,ITEMNAME,use_type)values('" + id
                       + "','" + filename + "','" + fileext + "','" + itemid + "','" + itemname + "','" + usetype + "')";
                Outida.RunSql(sql);
            }
            try
            {
                //string sql = @"insert into " + tablename + " (id,name,suffix,ITEMID,ITEMNAME,use_type,FILECONTENT)values('" + id
                //+ "','" + filename + "','" + fileext + "','" + itemid + "','" + itemname + "','" + usetype + "',:bFileContent)";
                //IDataParameter[] idp = new System.Data.OracleClient.OracleParameter[1];
                //idp[0] = new OracleParameter("bFileContent", OracleType.Blob, Length);
                //idp[0].Value = FileContent;
                //Outida.RunSql(sql, ref idp);
                //Outida.Close(true);
                OracleConnection oc = new OracleConnection();
                oc.ConnectionString = ZSConnectionString;
                OracleCommand cmd = new OracleCommand("update " + tablename + " set FILECONTENT=:bFileContent  where id='" + id + "'", oc);
                OracleParameter op1 = new OracleParameter("bFileContent", OracleType.Blob, FileContent.Length);
                op1.Value = FileContent;
                cmd.Parameters.Add(op1);
                oc.Open();
                cmd.ExecuteNonQuery();
                oc.Close();
            }
            catch
            {

            }
        }
        public override string GetFileId(string itemid)
        {
            string sql = "select id from zs_file where ITEMID='" + itemid + "' and use_type='0' and ITEMNAME='用户签名'";
            return Outida.GetValue(sql);
        }

        public override DataTable GetFANWEILOG(string pid, string proname, string username)
        {
            string sql = @"select t.*,(select name from zs_user where id=t.urid) 操作人,
(select 项目名称 from zs_project where id =t.pid) 项目名称 from zs_fanweilog t where 1=1 ";
            if (!string.IsNullOrEmpty(pid))
            {
                sql += " and t.pid ='" + pid + "'";
            }
            if (!string.IsNullOrEmpty(proname))
            {
                sql += " and t.pid in (select id from zs_project where 项目名称 like '%" + proname + "%')";
            }
            if (!string.IsNullOrEmpty(username))
            {
                sql += " and t.urid in (select id from zs_user where name like '%" + username + "%')";
            }
            sql += " order by t.更新时间 desc";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetJDXX(string sqlwhere, string sqlcolumn)
        {
            string sql = string.Format(@"select t.id,t.pid,t1.项目名称,t1.建设单位,t1.立项号,t.征收许可证号,
t1.征收范围,t1.征收实施单位 {0} from zs_prono t,zs_project t1 where t.pid=t1.id and t.审核状态=1 {1}", sqlcolumn, sqlwhere);
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override void UpdateCanSync(string tableName, string column, string ID, string query)
        {
            string sql = string.Format(@"update {0} set can_sync = 1 where {1}='{2}' {3}", tableName, column, ID, query);
            Outida.RunSql(sql);
        }
        public override void InsertZSFW(string pid, string userid, string useType, string useItemName)
        {
            string sql = @"select 征收范围 from zs_project where id='" + pid + "'";
            string temp = Outida.GetValue(sql);
            if (useType == "1")
            {
                sql = "select 冻结征收范围 from zs_project where id='" + pid + "'";
                temp = Outida.GetValue(sql);
            }
            else if (useType == "2")
            {
                sql = "select 征收范围 from zs_bcfaqs where 补偿实施='0' and pid='" + pid + "'";
                temp = Outida.GetValue(sql);
            }
            else if (useType == "3")
            {
                sql = "select 征收范围 from zs_bcfaqs where 补偿实施='1' and pid='" + pid + "'";
                temp = Outida.GetValue(sql);
            }
            else if (useType == "4")
            {
                sql = "select wm_concat(拆迁范围) from zs_prono where pid='" + pid + "'";
                temp = Outida.GetValue(sql);
            }
            string isql = string.Format(@"insert into ZS_FANWEILOG (pid,征收范围,更新时间,URID,业务类型,业务名称) 
values ('{0}','{1}',sysdate,'{2}','{3}','{4}')", pid, temp, userid, useType, useItemName);
            Outida.RunSql(isql);
        }
        public override bool IsZSAdmin(string userid)
        {
            string rolename = System.Configuration.ConfigurationSettings.AppSettings["IsZSAdmin"].ToString();
            string sql = @"Select Count(*) From zs_USER_IN_ROLE t Join  zs_Role tt  On  t.roleid=tt.id
        Where tt.rolename Like '%" + rolename + "%' And t.userid like '%" + userid + "%'";
            int isadmin = Convert.ToInt32(Outida.GetValue(sql));
            if (isadmin > 0)
            {
                return true;
            }
            return false;
        }
        public override bool IsQZSBM(string userid)
        {
            string rolename = System.Configuration.ConfigurationSettings.AppSettings["IsQZSBM"].ToString();
            string sql = @"Select Count(*) From zs_USER_IN_ROLE t Join  zs_Role tt  On  t.roleid=tt.id
        Where tt.rolename Like '%" + rolename + "%' And t.userid like '%" + userid + "%'";
            int isadmin = Convert.ToInt32(Outida.GetValue(sql));
            if (isadmin > 0)
            {
                return true;
            }
            return false;
        }
        public override bool IsSZSB(string userid)
        {
            string rolename = System.Configuration.ConfigurationSettings.AppSettings["IsSZSB"].ToString();
            string sql = @"Select Count(*) From zs_USER_IN_ROLE t Join  zs_Role tt  On  t.roleid=tt.id
        Where tt.rolename Like '%" + rolename + "%' And t.userid like '%" + userid + "%'";
            int isadmin = Convert.ToInt32(Outida.GetValue(sql));
            if (isadmin > 0)
            {
                return true;
            }
            return false;
        }
        public override bool IsQXLRRY(string userid)
        {
            string rolename = System.Configuration.ConfigurationSettings.AppSettings["IsQXLRRY"].ToString();
            string sql = @"Select Count(*) From zs_USER_IN_ROLE t Join  zs_Role tt  On  t.roleid=tt.id
        Where tt.rolename Like '%" + rolename + "%' And t.userid like '%" + userid + "%'";
            int isadmin = Convert.ToInt32(Outida.GetValue(sql));
            if (isadmin > 0)
            {
                return true;
            }
            return false;
        }
        public override bool IsQXZZ(string userid)
        {
            string rolename = System.Configuration.ConfigurationSettings.AppSettings["IsQXZZ"].ToString();
            string sql = @"Select Count(*) From zs_USER_IN_ROLE t Join  zs_Role tt  On  t.roleid=tt.id
        Where tt.rolename Like '%" + rolename + "%' And t.userid like '%" + userid + "%'";
            int isadmin = Convert.ToInt32(Outida.GetValue(sql));
            if (isadmin > 0)
            {
                return true;
            }
            return false;
        }
        public override bool IsNetZSAdmin(string userid)
        {
            string rolename = System.Configuration.ConfigurationSettings.AppSettings["IsNetZSAdmin"].ToString();
            string sql = @"Select Count(*) From ZS_USER_IN_ROLE t Join  ZS_Role tt  On  t.roleid=tt.id
        Where tt.rolename Like '%" + rolename + "%' And t.userid like '%" + userid + "%'";
            int isadmin = Convert.ToInt32(Outida.GetValue(sql));
            if (isadmin > 0)
            {
                return true;
            }
            return false;
        }
        public override bool IsSQLXPro(string pid)
        {
            string qxname = System.Configuration.ConfigurationSettings.AppSettings["IsSQLXPro"].ToString();
            string sql = "select count(*) from zs_project where id='" + pid + "' and 区属 in(" + qxname + ")";
            int issqlx = Convert.ToInt32(Outida.GetValue(sql));
            if (issqlx > 0)
            {
                return true;
            }
            return false;
        }
        public override bool DeleteXmByID(string tableName, string columname, string id)
        {
            string sqlStr = string.Format("delete from {0} where {1}='{2}'", tableName, columname, id);
            return Outida.RunSql(sqlStr) > 0 ? true : false;
        }
        public override void DeleteXXByID(string id)
        {
            string cksql = string.Format(@"select * from zs_prono where pid ='{0}'", id);
            DataTable dt = null;
            Outida.RunSql(cksql, out dt);
            if (dt.Rows.Count > 0)
            {
                string sqlStr = string.Format("delete from zs_prono where pid='{0}'", id);
                Outida.RunSql(sqlStr);
            }
            string sql1 = string.Format(@"select * from zs_proloupan where pid in (select id from zs_prono where pid ='{0}')", id);
            DataTable dt1 = null;
            Outida.RunSql(sql1, out dt1);
            if (dt1.Rows.Count > 0)
            {
                string sqlStr1 = string.Format(@"delete from zs_proloupan where pid in (select id from zs_prono where pid ='{0}')", id);
                Outida.RunSql(sqlStr1);
            }
            string sql2 = string.Format(@"select * from zs_prozhuang where pnid in (select id from zs_prono where pid ='{0}')", id);
            DataTable dt2 = null;
            Outida.RunSql(sql2, out dt2);
            if (dt2.Rows.Count > 0)
            {
                string sqlStr2 = string.Format(@"delete from zs_prozhuang where pnid in (select id from zs_prono where pid ='{0}')", id);
                Outida.RunSql(sqlStr2);
            }
            string sql3 = string.Format(@"select * from zs_bcbasicinfo where pnid in (select id from zs_prono where pid ='{0}')", id);
            DataTable dt3 = null;
            Outida.RunSql(sql3, out dt3);
            if (dt3.Rows.Count > 0)
            {
                string sqlStr3 = string.Format(@"delete from zs_bcbasicinfo where pnid in (select id from zs_prono where pid ='{0}')", id);
                Outida.RunSql(sqlStr3);
                for (int i = 0; i < dt3.Rows.Count; i++)
                {
                    string bcID = dt3.Rows[i]["ID"].ToString();
                    string cksql1 = string.Format(@"select * from zs_new_money where bid ='{0}'", bcID);
                    string cksql2 = string.Format(@"select * from zs_change_money where bid ='{0}'", bcID);
                    DataTable ddt1 = null;
                    Outida.RunSql(cksql1, out ddt1);
                    DataTable ddt2 = null;
                    Outida.RunSql(cksql2, out ddt2);
                    if (ddt1.Rows.Count > 0)
                    {
                        string sqlsstr1 = string.Format(@"delete from zs_new_money where bid ='{0}')", bcID);
                        Outida.RunSql(sqlsstr1);
                    }
                    if (ddt2.Rows.Count > 0)
                    {
                        string sqlsstr2 = string.Format(@"delete from zs_change_money where bid ='{0}')", bcID);
                        Outida.RunSql(sqlsstr2);
                    }
                }
            }
            string sql6 = string.Format(@"select * from ZS_PGGS where pid ='{0}'", id);
            DataTable dt6 = null;
            Outida.RunSql(sql6, out dt6);
            if (dt6.Rows.Count > 0)
            {
                string sqlStr6 = string.Format(@"delete from ZS_PGGS where pid ='{0}'", id);
                Outida.RunSql(sqlStr6);
            }
            string sql9 = string.Format(@"select * from zs_cqzhxy where fid in (select id from zs_proloupan where pid in (select id from zs_prono where pid='{0}'))", id);
            DataTable dt9 = null;
            Outida.RunSql(sql9, out dt9);
            if (dt9.Rows.Count > 0)
            {
                string sqlStr9 = string.Format(@"delete from zs_cqzhxy where fid in (select id from zs_proloupan where pid in (select id from zs_prono where pid='{0}'))", id);
                Outida.RunSql(sqlStr9);
            }
            string sql10 = string.Format(@"select * from zs_hbhxy where pnid in (select id from zs_prono where pid ='{0}')", id);
            DataTable dt10 = null;
            Outida.RunSql(sql10, out dt10);
            if (dt10.Rows.Count > 0)
            {
                string sqlStr10 = string.Format(@"delete from zs_hbhxy where pnid in (select id from zs_prono where pid ='{0}')", id);
                Outida.RunSql(sqlStr10);
            }
            string sql11 = string.Format(@"select * from ZS_XINFANG where pnid in (select id from zs_prono where pid='{0}') ", id);
            DataTable dt11 = null;
            Outida.RunSql(sql11, out dt11);
            if (dt11.Rows.Count > 0)
            {
                string sqlStr11 = string.Format(@"delete from ZS_XINFANG where pnid in (select id from zs_prono where pid='{0}')", id);
                Outida.RunSql(sqlStr11);
            }
            string sql12 = string.Format(@"select * from ZS_XINFANGZD where pnid in (select id from zs_prono where pid='{0}')", id);
            DataTable dt12 = null;
            Outida.RunSql(sql12, out dt12);
            if (dt12.Rows.Count > 0)
            {
                string sqlStr12 = string.Format(@"delete from ZS_XINFANGZD where pnid in (select id from zs_prono where pid='{0}')", id);
                Outida.RunSql(sqlStr12);
            }
            string sql13 = string.Format(@"select * from zs_file where itemid='{0}'", id);
            DataTable dt13 = null;
            Outida.RunSql(sql13, out dt13);
            if (dt13.Rows.Count > 0)
            {
                string sqlStr13 = string.Format(@"delete from zs_file where itemid='{0}'", id);
                Outida.RunSql(sqlStr13);
            }
            string sql14 = string.Format(@"select * from zs_fanweilog where pid='{0}'", id);
            DataTable dt14 = null;
            Outida.RunSql(sql14, out dt14);
            if (dt14.Rows.Count > 0)
            {
                string sqlStr14 = string.Format(@"delete from zs_fanweilog where pid='{0}'", id);
                Outida.RunSql(sqlStr14);
            }
            string sql15 = string.Format(@"select * from zs_bcfaqs where pid='{0}'", id);
            DataTable dt15 = null;
            Outida.RunSql(sql15, out dt15);
            if (dt15.Rows.Count > 0)
            {
                string sqlStr15 = string.Format(@"delete from zs_bcfaqs where pid='{0}'", id);
                Outida.RunSql(sqlStr15);
            }
            string sql16 = string.Format(@"select * from zs_xydyry where xyid in (select id from zs_hbhxy where pnid in (
select id from zs_prono where pid='{0}'))", id);
            DataTable dt16 = null;
            Outida.RunSql(sql16, out dt16);
            if (dt16.Rows.Count > 0)
            {
                string sqlStr16 = string.Format(@"delete from zs_xydyry where xyid in (select id from zs_hbhxy where pnid in (
select id from zs_prono where pid='{0}'))", id);
                Outida.RunSql(sqlStr16);
            }
            string sql17 = string.Format(@"select * from zs_zspch where pnid in (select id from zs_prono where pid ='{0}')", id);
            DataTable dt17 = null;
            Outida.RunSql(sql17, out dt17);
            if (dt17.Rows.Count > 0)
            {
                string sqlStr17 = string.Format(@"delete from zs_zspch where pnid in (select id from zs_prono where pid ='{0}')", id);
                Outida.RunSql(sqlStr17);
            }
            string sql18 = string.Format(@"select * from ZS_BCJD where pnid in (select id from zs_prono where pid ='{0}')", id);
            DataTable dt18 = null;
            Outida.RunSql(sql18, out dt18);
            if (dt18.Rows.Count > 0)
            {
                string sqlStr18 = string.Format(@"delete from ZS_BCJD where pnid in (select id from zs_prono where pid ='{0}')", id);
                Outida.RunSql(sqlStr18);
            }
            string sql19 = string.Format(@"select * from ZS_YBBTJ where pnid in (select id from zs_prono where pid ='{0}')", id);
            DataTable dt19 = null;
            Outida.RunSql(sql19, out dt19);
            if (dt19.Rows.Count > 0)
            {
                string sqlStr19 = string.Format(@"delete from ZS_YBBTJ where pnid in (select id from zs_prono where pid ='{0}')", id);
                Outida.RunSql(sqlStr19);
            }
            string sql20 = string.Format(@"select * from zs_zzbcjfenhu where pid in (select id from zs_prono where pid ='{0}')", id);
            DataTable dt20 = null;
            Outida.RunSql(sql20, out dt20);
            if (dt20.Rows.Count > 0)
            {
                string sqlStr20 = string.Format(@"delete from zs_zzbcjfenhu where pid in (select id from zs_prono where pid ='{0}')", id);
                Outida.RunSql(sqlStr20);
            }
            string sql21 = string.Format(@"select * from zs_pgfbsq where pnid in (select id from zs_prono where pid ='{0}')", id);
            DataTable dt21 = null;
            Outida.RunSql(sql21, out dt21);
            if (dt21.Rows.Count > 0)
            {
                string sqlStr21 = string.Format(@"delete from zs_pgfbsq where pnid in (select id from zs_prono where pid ='{0}')", id);
                Outida.RunSql(sqlStr21);
            }
            string sql22 = string.Format(@"select * from ZS_PGBMFBXX where pnid in (select id from zs_prono where pid ='{0}')", id);
            DataTable dt22 = null;
            Outida.RunSql(sql22, out dt22);
            if (dt22.Rows.Count > 0)
            {
                string sqlStr22 = string.Format(@"delete from ZS_PGBMFBXX where pnid in (select id from zs_prono where pid ='{0}')", id);
                Outida.RunSql(sqlStr22);
            }
            string sql23 = string.Format(@"select * from ZS_PGBMJL where pnid in (select id from zs_prono where pid ='{0}')", id);
            DataTable dt23 = null;
            Outida.RunSql(sql23, out dt23);
            if (dt23.Rows.Count > 0)
            {
                string sqlStr23 = string.Format(@"delete from ZS_PGBMJL where pnid in (select id from zs_prono where pid ='{0}')", id);
                Outida.RunSql(sqlStr23);
            }
        }
        public override DataTable GetPronoXX(string pid, string sqlWhere)
        {
            string sqlStr = @"select t.id,t.pid,t1.项目名称,t1.建设单位,t1.立项号,t.征收许可证号,
t1.征收范围,t.提交状态,t.审核状态 from zs_prono t,zs_project t1 where t.pid=t1.id 
and t.征收许可证号 is not null and t.pid = '" + pid + "' " + sqlWhere;
            DataTable dt = null;
            Outida.RunSql(sqlStr, out dt);
            return dt;
        }
        public override Prono GetProno(string pnid)
        {
            Prono project = new Prono();
            string sql = @"select * from zs_prono where id ='" + pnid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return project = DataHelper.DataTableToTOne<Prono>(dt);
        }
        public override BCFAQS GetBCFAQS(string type, string pid)
        {
            BCFAQS bcfaqs = new BCFAQS();
            string sql = "select * from zs_bcfaqs where 补偿实施='" + type + "' and pid='" + pid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return bcfaqs = DataHelper.DataTableToTOne<BCFAQS>(dt);
        }
        public override bool CheckData(string pid, string zid)
        {
            string sql = string.Format(@"select * from zs_cqzhxy where fid in (
select id from zs_proloupan where pid='{0}' and zid='{1}')", pid, zid);
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt.Rows.Count > 0 ? true : false;
        }
        public override DataTable GetProZhuang(string pid, string sqlwhere)
        {
            string sql = string.Format(@"select t.ID,t.PNID,t.地块号,t.幢号,t.单元号,
(to_number(nvl((select sum(to_number(nvl(replace(p.建筑面积, '\', ''), 0)))
          from zs_proloupan p
         where p.zid = t.id and p.id in (select fid from zs_cqzhxy )
           and p.STATUS = 1),0))) 面积,
(select count(*) from zs_proloupan p where p.zid=t.id and p.STATUS=1) 套数 from zs_prozhuang t where t.pnid='{0}' ", pid);
            if (!string.IsNullOrEmpty(sqlwhere))
            {
                sql += sqlwhere;
            }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override ProZhuang GetProZhuang(string zid)
        {
            ProZhuang prozhuang = new ProZhuang();
            string sql = "select * from zs_prozhuang where id='" + zid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return prozhuang = DataHelper.DataTableToTOne<ProZhuang>(dt);
        }
        public override DataTable GetProloupan(string zid)
        {
            string sql = "select  distinct 楼层号 from zs_proloupan t  where zid='" + zid + "' order by 楼层号 desc ";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override List<ProLoupan> GetProLoupan(string lcno, string zid)
        {
            List<ProLoupan> proloupanlist = new List<ProLoupan>();
            string sql = "select * from zs_proloupan where 楼层号=" + lcno + " and zid='" + zid + "' order by  楼层房间号 desc ";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            proloupanlist = DataHelper.DataTableToT<ProLoupan>(dt);
            return proloupanlist;
        }
        public override ProLoupan GetProLoupanRoom(string id)
        {
            ProLoupan proloupan = new ProLoupan();
            string sql = "select * from zs_proloupan where id ='" + id + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return proloupan = DataHelper.DataTableToTOne<ProLoupan>(dt);
        }
        public override DataTable GetLoupanRoom(string pid, string zid)
        {
            string sql = @"select z.产权人,z.承租人,z.房屋建筑面积,p.建筑面积,p.楼层房间号 from zs_proloupan p,zs_zsperson z where p.zspid=z.id 
and p.pid = '" + pid + "' and p.zid='" + zid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override int GetLoupanType(string cloumname, string zid, int roomno, int index)
        {
            int tt = -1;
            string sql = "select " + cloumname + " from zs_proloupan t where zid='" + zid + "' and 楼层房间号='" + roomno + "'||lpad('" + index + @"',2,'0') ";
            if (!string.IsNullOrEmpty(Outida.GetValue(sql)))
            {
                tt = Convert.ToInt32(Outida.GetValue(sql));
            }
            return tt;
        }
        public override void UpdateLoupan(string columname, string zid, int roomno, int index)
        {
            string sql = @"update  zs_proloupan t set  " + columname + "=1 where zid='" + zid + "' and 楼层房间号='" + roomno + "'||lpad('" + index + @"',2,'0') ";
            Outida.RunSql(sql);
        }
        public override void InsertLoupan(int aztype, string pid, string zid, int roomno, int index, int status)
        {
            string sql = @"insert into zs_proloupan t (安置状态,PID, 楼层号, 房间号,楼层房间号,zid,status)
values('" + aztype + "','" + pid + @"', '" + roomno + @"','" + index + @"','" + roomno + "'||lpad('" + index + @"',2,'0') ,'" + zid + "' ,'" + status + "' )";
            if (status == 9)
            {
                sql = @"insert into zs_proloupan t (安置状态,PID, 楼层号, 房间号,楼层房间号,zid)
values('" + aztype + "','" + pid + @"', '" + roomno + @"','" + index + @"','" + roomno + "'||lpad('" + index + @"',2,'0') ,'" + zid + "'  )";
            }
            Outida.RunSql(sql);
        }
        public override string GetSomeStr(string tablename, string columname, string newcolumname, string newid)
        {
            string sql = "select " + columname + " from " + tablename + " where " + newcolumname + "='" + newid + "'";
            string temp = Outida.GetValue(sql);
            return temp;
        }
        public override string GetSomeWhere(string tablename, string columname, string strwhere)
        {
            string sql = "select " + columname + " from " + tablename + " where " + strwhere + "";
            string temp = Outida.GetValue(sql);
            return temp;
        }
        public override bool ChangeZT(string tablename, string columnname, string strid, string tt)
        {
            string sqlStr = string.Format("update {0} set {1}='{2}' where id='{3}'", tablename, columnname, tt, strid);
            return Outida.RunSql(sqlStr) > 0 ? true : false;
        }
        public override bool UpdateBSZT(string table, string column, string value, string wcolumn, string wvalue)
        {
            string sqlStr = string.Format("update {0} set {1}='{2}' where {3}='{4}'", table, column, value, wcolumn, wvalue);
            return Outida.RunSql(sqlStr) > 0 ? true : false;
        }
        public override void UpdateSomething(string tablename, string column, string value, string strcolumn, string strvalue)
        {
            string sql = "update " + tablename + " set " + column + " ='" + value + "' where " + strcolumn + "='" + strvalue + "'";
            Outida.RunSql(sql);
        }
        public override void InsertSomething(string tablename, string column, string value)
        {
            string sql = "insert into " + tablename + " (" + column + ") values (" + value + ")";
            Outida.RunSql(sql);
        }
        public override string GetSXNo(string type, string strid)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(type)) { type = "0"; }
            if (type == "0")
            {
                sql = @"Select (Case When max(to_number(TEXTBOX2)) Is Null Then 0
Else max(to_number(TEXTBOX2)) End) num From zs_cqzhxy Where fid ='" + strid + "'";
            }
            else if (type == "1")
            {
                sql = @"Select (Case When max(to_number(TEXTBOX1)) Is Null Then 0
Else max(to_number(TEXTBOX1)) End) num From zs_hbhxy Where pnid='" + strid + "'";
            }
            int zsbmhave = Convert.ToInt32(Outida.GetValue(sql));
            int snum = 1;
            string username = "000" + snum;
            if (zsbmhave > 0)
            {
                username = "000" + (zsbmhave + 1);
            }
            if (username.Length == 8)
            {
                username = username.Substring(4, 4);
            }
            else if (username.Length == 7)
            {
                username = username.Substring(3, 4);
            }
            else if (username.Length == 6)
            {
                username = username.Substring(2, 4);
            }
            else if (username.Length == 5)
            {
                username = username.Substring(1, 4);
            }
            return username;
        }
        public override string GetXYIID()
        {
            string tIID = "310000000000";
            string sql = @"select max(IID) iid from ((select IID from zs_cqzhxy) union all (select iid from zs_hbhxy ))";
            string strIID = Outida.GetValue(sql);
            if (!string.IsNullOrEmpty(strIID))
            {
                long temp = Convert.ToInt64(strIID);
                long tt = temp + 1;
                return tt.ToString();
            }
            else
            {
                return tIID;
            }
        }
        public override YJCS GetYJCS()
        {
            YJCS yjcs = new YJCS();
            string sql = "select * from zs_yjcs where 1=1 ";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return yjcs = DataHelper.DataTableToTOne<YJCS>(dt);
        }
        public override DataTable GetBZSR(string strWhere)
        {
            string sql = @"select t.id,
(Case When t.pid Is Not Null Then (
(Select 项目名称 From zs_project Where Id=t.pid)
)Else (
Select 项目名称 From zs_project Where Id In (
Select pid From zs_prono Where Id In (Select pid From zs_zzbcjfenhu Where zspid=t.id))
) End ) 项目名称,
产权人,证件号,丘权号,权号,房屋坐落,房屋建筑面积 面积,承租人,
(select keyname from zs_params where name='房屋产权证证载用途' and keyvalue=to_number(nvl(t.房屋用途,0))) 房屋产权证证载用途,
(select keyname from zs_params where name='结构等级' and keyvalue=to_number(nvl(t.房屋结构,0))) 房屋结构,
使用性质,所属层次,房屋权证编号,土地权证编号,土地分摊面积,征收区位等级,所属类别,使用面积,
(select keyname from zs_params where name='证件类型' and keyvalue=to_number(nvl(t.证件类型,0))) 证件类型
 from zs_zsperson t where 1=1 ";
            if (!string.IsNullOrEmpty(strWhere))
            {
                sql += strWhere;
            }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override bool GetBZSRXY(string personid)
        {
            string sql = string.Format(@"select distinct * from ((select personid from zs_hbhxy
          where personid = '{0}') union all (select zspid from zs_proloupan
          where id in (select fid from zs_cqzhxy) and zspid = '{0}'))", personid);
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt.Rows.Count > 0 ? true : false;
        }
        public override string GetBZSRZJH()
        {
            //string bzsrno = string.Empty;
            //string sql = @"select distinct 证件号 from zs_zsperson where is_number(证件号)='Y'";
            //DataTable dt = null;
            //Outida.RunSql(sql, out dt);
            //string tt = string.Empty;
            //List<int> mylist = new List<int>();
            //if (dt.Rows.Count > 0)
            //{
            //    for (int i = 0; i < dt.Rows.Count; i++)
            //    {
            //        tt = dt.Rows[i]["证件号"].ToString();
            //        if (tt.Length < 14)
            //        {
            //            if (tt.Length > 0)
            //            {
            //                mylist.Add(Convert.ToInt32(tt));
            //            }
            //        }
            //    }
            //}
            //int tt1 = 0;
            //if (mylist.Count > 0)
            //{
            //    mylist.Sort();
            //    tt1 = mylist[mylist.Count - 1];
            //}
            //return (tt1 + 1).ToString();
            string sql = "select distinct 证件号 from zs_zsperson where is_number(证件号)='Y' and length(证件号)<14 order by to_number(证件号) desc ";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            int tt = 0;
            if (dt.Rows.Count > 0)
            {
                tt = int.Parse(dt.Rows[0]["证件号"].ToString()) + 1;
            }
            else
            {
                tt = 1;
            }
            return tt.ToString();
        }
        public override bool GetRoomXY(string fid)
        {
            string sql = string.Format(@"select * from zs_cqzhxy where fid ='{0}'", fid);
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt.Rows.Count > 0 ? true : false;
        }
        public override List<ProLoupan> GetProloupanRom(string type, string pid, string strwhere)
        {
            List<ProLoupan> proloupanlist = new List<ProLoupan>();
            string sql = "Select z.*,(c.ZSJDH||c.textbox2) xyh From zs_proloupan z ,zs_cqzhxy c  where z.楼盘类型='" + type + "' and z.pid='" + pid + "' and z.Id = c.fid ";
            if (!string.IsNullOrEmpty(strwhere))
            {
                sql += strwhere;
            }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            proloupanlist = DataHelper.DataTableToT<ProLoupan>(dt);
            return proloupanlist;
        }
        public override DataTable GetZSPerson(string strType, string strwhere)
        {
            string strcolumn = @" ID,产权人,丘权号,房屋坐落,权号,房屋建筑面积,承租人,
(select keyname from zs_params where name='房屋用途' and keyvalue=to_number(nvl(t.房屋用途,0))) 房屋用途,使用性质,
(select keyname from zs_params where name='结构等级' and keyvalue=to_number(nvl(t.房屋结构,0))) 房屋结构,
所属层次,房屋权证编号,土地权证编号,土地分摊面积,证件号 ";
            string tablename = "zs_zsperson";
            if (!string.IsNullOrEmpty(strType))
            {
                if (strType == "zzhz")//住宅汇总
                {
                    strcolumn = @" ID,产权人,丘权号,房屋坐落,权号,房屋建筑面积,证件号,承租人,
(select keyname from zs_params where name='结构等级' and keyvalue=to_number(nvl(t.房屋结构,0))) 房屋结构,所属层次 ";
                }
                if (strType == "fzzhz")//非住宅汇总
                {
                    strcolumn = @" ID,产权人,丘权号,房屋坐落,权号,房屋建筑面积,承租人,房屋结构 房屋建筑结构,所属层次,
(select keyname from zs_params where name='房屋用途' and keyvalue=to_number(nvl(t.房屋用途,0))) 房屋用途,所属类别 ";
                }
                if (strType == "zzfh")//住宅分户评估
                {
                    strcolumn = @" ID,产权人,丘权号,房屋坐落,房屋权证编号,房屋建筑面积,承租人,
所属层次,土地权证编号,土地分摊面积,征收区位等级,证件号 ";
                }
                if (strType == "yyfh")//营业性房屋评估
                {
                    strcolumn = @" ID,产权人,丘权号,房屋坐落,房屋权证编号,房屋建筑面积,承租人,
(select keyname from zs_params where name='房屋用途' and keyvalue=to_number(nvl(t.房屋用途,0))) 房屋用途,使用性质,
(select keyname from zs_params where name='结构等级' and keyvalue=to_number(nvl(t.房屋结构,0))) 房屋结构,
所属层次,土地权证编号,土地分摊面积,征收区位等级,所属类别,证件号 ";
                }
                if (strType == "fyyfh")//非营业性
                {
                    strcolumn = @" ID,产权人,丘权号,房屋坐落,房屋权证编号,房屋建筑面积,承租人,
(select keyname from zs_params where name='房屋用途' and keyvalue=to_number(nvl(t.房屋用途,0))) 房屋用途,使用性质,
(select keyname from zs_params where name='结构等级' and keyvalue=to_number(nvl(t.房屋结构,0))) 房屋结构,
所属层次,土地权证编号,土地分摊面积,征收区位等级,所属类别,证件号 ";
                }
                if (strType == "snfh")//装饰装修
                {
                    strcolumn = @" ID,产权人,丘权号,房屋坐落,房屋权证编号,房屋建筑面积,承租人,使用性质,
(select keyname from zs_params where name='结构等级' and keyvalue=to_number(nvl(t.房屋结构,0))) 房屋结构,
所属层次,征收区位等级,证件号 ";
                }
                if (strType == "bdry")//产权调换合同备案时绑定人员
                {
                    strcolumn = @" ID,产权人,承租人,房屋坐落 ";
                }
                if (strType == "xfzdry")
                {
                    strcolumn = @" ID,姓名,性别,身份证号,住址,电话,单位,to_char(信访时间,'yyyy-MM-dd') 信访时间,pnid ";
                    tablename = "zs_xinfang";
                }
            }
            string sql = "select " + strcolumn + " from " + tablename + " t where 1=1 " + strwhere + "";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetProjectJD(string strwhere)
        {
            string sql = @" select t1.项目名称,t1.建设单位,t1.立项号,t.征收许可证号,t1.征收实施单位,t1.征收部门,
 t1.征收范围,t.id,t1.id pid from zs_project t1,zs_prono t where t1.id=t.pid and t.审核状态=1 ";
            if (!string.IsNullOrEmpty(strwhere)) { sql += strwhere; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override void AddCollectRow(DataTable dtSource, string strRowNoField, List<string> lstNeedCollectFields)
        {
            //整合构建datable,添加行标记、并统计企业总数和注册资本
            double[] arrTempDouble = new double[lstNeedCollectFields.Count];

            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                dtSource.Rows[i][strRowNoField] = i + 1;

                for (int k = 0; k < lstNeedCollectFields.Count; k++)
                {
                    string strCollectField = lstNeedCollectFields[k];

                    if (!Convert.IsDBNull(dtSource.Rows[i][strCollectField]))
                    {
                        arrTempDouble[k] += Convert.ToDouble(dtSource.Rows[i][strCollectField]);
                    }
                    else
                    {
                        arrTempDouble[k] += 0;
                    }
                }
            }

            //添加统计行
            DataRow drNewRow = dtSource.NewRow();
            drNewRow[strRowNoField] = "合计";

            for (int k = 0; k < lstNeedCollectFields.Count; k++)
            {
                string strCollectField = lstNeedCollectFields[k];
                drNewRow[strCollectField] = arrTempDouble[k];
            }
            dtSource.Rows.Add(drNewRow);
        }
        public override DataTable GetBCZJ(string strwhere1, string strwhere2)
        {
            string sql = string.Format(@"select p2.立项号,p2.项目名称,p2.pnid,
p.开户银行,  
p.报结情况, 
replace(p.到账金额, '\', '') 到账金额,  
replace(p.划转金额, '\', '') 划转金额,  
replace(p.余额, '\', '') 余额,  
replace(p.概算总金额, '\', '') 概算总金额,  
p.ID 编号,  
p.ID  
  from (select 立项号, 项目名称, id pnid,评估机构,征收实施单位,区属 from zs_project p2 where 提醒开户状态=1 {0}) p2
  left join (select p.开户银行,
                    p.报结情况,
                    (select sum(to_number(nvl(replace(到账金额, '\', ''), 0)))
                       from zs_new_money
                      where bid = p.id) 到账金额,
                    (select sum(to_number(nvl(replace(划转金额, '\', ''), 0)))
                       from zs_change_money
                      where bid = p.id) 划转金额,
                    (select (to_number(nvl((select sum(to_number(nvl(replace(到账金额, '\', ''),
                                                                    0)))
                                             from zs_new_money
                                            where bid = p.id),
                                           0)) -
                            to_number(nvl((select sum(to_number(nvl(replace(划转金额, '\', ''),
                                                                    0)))
                                             from zs_change_money
                                            where bid = p.id),
                                           0)))
                       from dual) 余额,
                    to_number(nvl((select sum(replace(概算补偿总金额, '\', ''))
                                    from ZS_PGGS
                                   where pid = p.pnid),
                                  0)) 概算总金额,
                    p.id 编号,
                    p.id,
                    p.pnid,p.银行账号,p.时间 
               from ZS_BCBASICINFO p where 1=1 {1}) p on p.pnid = p2.pnid ", strwhere1, strwhere2);
            if (!string.IsNullOrEmpty(strwhere2))
            {
                sql = string.Format(@"select p2.立项号,
       p2.项目名称,
       p2.id pnid,
       p.开户银行,
       p.报结情况,
       (select sum(to_number(nvl(replace(到账金额, '\', ''), 0)))
          from zs_new_money
         where bid = p.id) 到账金额,
       (select sum(to_number(nvl(replace(划转金额, '\', ''), 0)))
          from zs_change_money
         where bid = p.id) 划转金额,
       (select (to_number(nvl((select sum(to_number(nvl(replace(到账金额, '\', ''), 0)))
                                from zs_new_money
                               where bid = p.id),
                              0)) -
               to_number(nvl((select sum(to_number(nvl(replace(划转金额, '\', ''), 0)))
                                from zs_change_money
                               where bid = p.id),
                              0)))
          from dual) 余额,
       to_number(nvl((select sum(replace(概算补偿总金额, '\', ''))
                       from ZS_PGGS
                      where pid = p.pnid),
                     0)) 概算总金额,
       p.ID 编号,
       p.ID
  from zs_project p2, ZS_BCBASICINFO p
 where p.pnid = p2.id {0} {1}", strwhere1, strwhere2);
            }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override string GetCompensateBasicNum(string strwhere1, string strwhere2)
        {
            string sql = string.Format(@"select p2.立项号,p2.项目名称,p2.id pnid,
p.开户银行,p.报结情况,NULL AS 到账金额,NULL AS 划转金额,NULL AS 余额,NULL AS 概算总金额,p.ID 编号,p.ID
  from (select * from zs_project p2 where p2.提醒开户状态=1 {0}) p2 left join (select * from ZS_BCBASICINFO p where 1=1 {1}) p
 on p.pnid = p2.id ", strwhere1, strwhere2);
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt.Rows.Count.ToString();
        }
        public override string getXYBCK(string pnid)
        {
            string temp = string.Empty;
            string sql = string.Format(@"select to_number(nvl((select sum(to_number(nvl(replace(t.TEXTBOX19, '\', ''), 0)) +
                   to_number(nvl(replace(t.TEXTBOX40, '\', ''), 0)) +
                   to_number(nvl(replace(t.TEXTBOX48, '\', ''), 0)) +
                   to_number(nvl(replace(t.TEXTBOX51, '\', ''), 0)) +
                   to_number(nvl(replace(t.TEXTBOX37, '\', ''), 0))) hb
          from zs_hbhxy t
         where t.pnid in (select id from zs_prono where pid='{0}') ),0)) +
       to_number(nvl((select sum(to_number(nvl(replace(z.TEXTBOX21, '\', ''), 0)) +
                   to_number(nvl(replace(z.TEXTBOX38, '\', ''), 0)) +
                   to_number(nvl(replace(z.TEXTBOX41, '\', ''), 0)) +
                   to_number(nvl(replace(z.TEXTBOX49, '\', ''), 0)) +
                   to_number(nvl(replace(z.TEXTBOX52, '\', ''), 0))) cq
          from zs_cqzhxy z
         where z.fid in
               (select id
                  from zs_proloupan z1
                 where z1.pid in (select id from zs_prono where pid='{0}'))),0)) 款 from dual", pnid);
            DataTable dt = null;
            temp = Outida.GetValue(sql);
            return temp;
        }
        public override DataTable GetSelProject(string temp, string strwhere, string qs)
        {
            string sqlStr = @"select t1.ID,t.立项号,t.项目名称,t.征收实施单位,t1.征收许可证号 征收决定号,t1.征收评估机构,t.征收部门 
from zs_project t,zs_prono t1 where t1.pid=t.id and t1.审核状态=1";;
            string strsql = " and t.id not in(select 关联ID from xt_instance_ext ix,st_instance i where ix.iid=i.iid and i.wid=(select wid from st_workflow where  rot=0 and wname='" + temp + "') )";
            string strpsql = "  id not in(select 关联ID from xt_instance_ext ix,st_instance i where ix.iid=i.iid and i.wid=(select wid from st_workflow where  rot=0 and wname='" + temp + "') )";
            //if (string.IsNullOrEmpty(temp)) { temp = "xzxm"; }
            if (temp.IndexOf("房屋征收程序申请") > -1 || temp.IndexOf("房屋征收立项申请") > -1)
            {
                sqlStr = "Select t.id,t.立项号,t.项目名称,t.建设单位,t.项目地址,t.征收部门 From zs_project t where t.提交状态=1 and t.审批状态<>1 " + strsql + strwhere + "";
            }
            if (temp.IndexOf("房屋征收冻结申请") > -1)
            {
                sqlStr = "Select t.id,t.立项号,t.项目名称,t.建设单位,t.项目地址,t.征收部门 From zs_project t where t.审批状态=1 and t.冻结状态=1 and t.冻结审批状态<>1 " + strsql + strwhere + "";
            }
            if (temp.IndexOf("房屋征收补偿实施方案申请") > -1) //t.冻结审批状态=1 and and t.补偿审批状态=1 
            {
                sqlStr = "Select t.id,t.立项号,t.项目名称,t.建设单位,t.项目地址,t.征收部门 From zs_project t where  t.补偿实施提交状态=1  and t.补偿实施审批状态<>1 " + strsql + strwhere + "";
            }
            if (temp.IndexOf("房屋征收补偿方案申请")>-1)
            {
                //sqlStr = "Select t.id,t.立项号,t.项目名称,t.建设单位,t.项目地址,t.征收部门 From zs_project t where t.补偿提交状态=1 and t.冻结审批状态=1 and t.补偿审批状态<>1" + strsql + strwhere + "";
                sqlStr = "Select t.id,t.立项号,t.项目名称,t.建设单位,t.项目地址,t.征收部门 From zs_project t where t.补偿提交状态=1 and t.补偿审批状态<>1" + strsql + strwhere + "";
            }
            if (temp.IndexOf("房屋征收决定申请") > -1)
            {
                sqlStr = @"Select t1.ID,t.立项号,t.项目名称,t.征收实施单位,t1.征收许可证号 征收决定号,t1.征收评估机构,t.征收部门 
 From zs_project t,zs_prono t1 Where t.id=t1.pid and t.补偿实施审批状态=1 and t1.提交状态=1 and t1.审核状态<>1 " + strsql + strwhere + "";
                if (temp.IndexOf("(区县)") > -1)
                {
                    sqlStr = @"Select t1.ID,t.立项号,t.项目名称,t.征收实施单位,t1.征收许可证号 征收决定号,t1.征收评估机构,t.征收部门 
 From zs_project t,zs_prono t1 Where t.id=t1.pid and t.补偿实施审批状态=1 and t1.提交状态=1 and t1.审核状态<>1 
 and t.区属 in (" + System.Configuration.ConfigurationSettings.AppSettings["IsSQLXPro"].ToString() + ") " + strsql + strwhere;
                }
            }
            if (temp.IndexOf("房屋征收补偿决定审批") > -1)
            {
                sqlStr = @"Select Rownum 序号, Id, 产权人, 承租人, 证件号, 房屋坐落 From Zs_Zsperson Where "+strpsql+@" and Id Not In ((Select Personid From Zs_Hbhxy) Union
(Select Zspid From Zs_Proloupan Where Status = 1 And Zspid Is Not Null And Id In (Select Fid From Zs_Cqzhxy))) and id in (select zspid from zs_zzbcjfenhu ) " + strwhere + "";
            }
            if (temp == "xzxmzj")
            {
                sqlStr = @"select t.ID,t.立项号,t.项目名称,t.征收部门,t.征收实施单位  
from zs_project t where t.立项号 is not null "+strwhere;
            }
            if (temp == "房屋征收补偿资金监管")
            {
                sqlStr = @"select t.id ,
(select 立项号 from zs_project where id=t.pnid) 立项号,
t.征收实施单位,
t.开户银行,
t.存折金额,
t.银行账号,
t.余额
from ZS_BCBASICINFO t where t.开户审核状态=1 " + strwhere;
            }
            if (temp == "xzxmx")
            {
                string sqlWhere = string.Empty;
                if (!string.IsNullOrEmpty(qs))
                {
                    if (qs == "5")
                    {
                        sqlWhere = " and 区属 in ('秦淮区','白下区')";
                    }
                    else if (qs == "2")
                    {
                        sqlWhere = " and 区属 in ('鼓楼区','下关区')";
                    }
                    else 
                    {
                        string mysql = @"select keyname from zs_params where name='区属' and keyvalue='" + qs + "'";
                        string strQS = Outida.GetValue(mysql);
                        if (!string.IsNullOrEmpty(strQS)) { sqlWhere = " and 区属 ='" + strQS + "'"; }
                    }
                }
                sqlStr = string.Format(@"select t1.ID,t1.征收许可证号 征收决定号,t.征收部门,t.征收实施单位,t.项目地址,t.项目名称,
nvl((select 权证户数 from zs_bcfaqs where 补偿实施=0 and pid=t.id),0) 居民户数,
nvl((select 工企单位 from zs_bcfaqs where 补偿实施=0 and pid=t.id),0) 工企单位,
nvl((Select Round((to_number(nvl(房屋建筑面积,0))/10000),2) from zs_bcfaqs where 补偿实施=0 and pid=t.id),0) 房屋建筑面积,
(select 征收签约期限 from zs_bcfaqs where 补偿实施=0 and pid=t.id) 征收期限 
 from zs_project t,zs_prono t1 where t1.pid=t.id {0} {1} and t1.审核状态=1 ", sqlWhere,strwhere);
            }
            if (temp == "xzxc")
            {
                sqlStr = @"select t.ID,t.立项号,t.项目名称,t.征收实施单位,t1.征收许可证号 征收决定号,t1.征收评估机构 
from zs_project t,zs_prono t1 where t1.pid=t.id and t1.审核状态=1 "+strwhere;
            }
            if (temp == "khyh")
            {
                sqlStr = @"select z.ID,z.单位名称,z.单位编号,z.地址 from zs_company z where z.单位类别='银行机构' ";
            }
            if (temp == "jftz")
            {
                sqlStr = @"select t1.id,t.立项号,t.项目名称,t.建设单位,t.项目地址,t1.征收许可证号 征收决定号 
from zs_project t,zs_prono t1 where t.id=t1.pid and t1.审核状态=1 "+strwhere;
            }
            if (temp == "sfxx")
            {
                sqlStr = @"select t.id,t.立项号,t.项目名称,to_char(t.addtime,'yyyy-mm-dd') 征收时间,t.项目地址,
(select 征收许可证号 from zs_prono where pid=t.id and rownum=1) 征收决定号 from zs_project t where 1=1 " + strwhere;
            }
            if (temp == "xfzdry")
            {
                sqlStr = @"select t1.id,t.项目名称,t.项目地址,t1.拆迁期限 征收期限,t1.征收许可证号 征收决定号,t1.实施单位联系人 征收人,
t.征收实施单位,t.评估机构,t.区属,t.立项号 from zs_project t,zs_prono t1 where t.id=t1.pid and t1.审核状态=1 "+strwhere;
            }
            if (string.IsNullOrEmpty(sqlStr))
            {
                sqlStr = @"select t.id,t.立项号,t.项目名称,t.建设单位,t.项目地址 from zs_project t where t.id in 
(select pid from zs_prono where 审核状态=1) "+strwhere;
            }
            DataTable dt = null;
            Outida.RunSql(sqlStr, out dt);
            return dt;
        }
        public override DataTable GetSelProject1(string temp, string strwhere, string qs)
        {
            string sqlStr = @"select t1.ID,t.立项号,t.项目名称,t.征收实施单位,t1.征收许可证号 征收决定号,t1.征收评估机构,t.征收部门 
from zs_project t,zs_prono t1 where t1.pid=t.id and t1.审核状态=1"; ;
            string strsql = " and t.id not in(select 关联ID from xt_instance_ext ix,st_instance i where ix.iid=i.iid and i.wid=(select wid from st_workflow where  rot=0 and wname='" + temp + "') )";
            string strpsql = "  id not in(select 关联ID from xt_instance_ext ix,st_instance i where ix.iid=i.iid and i.wid=(select wid from st_workflow where  rot=0 and wname='" + temp + "') )";
            //if (string.IsNullOrEmpty(temp)) { temp = "xzxm"; }
            if (temp.IndexOf("房屋征收程序申请") > -1 || temp.IndexOf("房屋征收立项申请") > -1)
            {
                sqlStr = "Select t.id,t.立项号,t.项目名称,t.建设单位,t.项目地址,t.征收部门 From zs_project t where t.提交状态=1 and t.审批状态=1 " + strsql + strwhere + "";
            }
            if (temp.IndexOf("房屋征收冻结申请") > -1)
            {
                sqlStr = "Select t.id,t.立项号,t.项目名称,t.建设单位,t.项目地址,t.征收部门 From zs_project t where t.审批状态=1 and t.冻结状态=1 and t.冻结审批状态=1 " + strsql + strwhere + "";
            }
            if (temp.IndexOf("房屋征收补偿实施方案申请") > -1)
            {
                sqlStr = "Select t.id,t.立项号,t.项目名称,t.建设单位,t.项目地址,t.征收部门 From zs_project t where t.冻结审批状态=1 and t.补偿实施提交状态=1 and t.补偿审批状态=1  and t.补偿实施审批状态=1 " + strsql + strwhere + "";
            }
            if (temp.IndexOf("房屋征收补偿方案申请") > -1)
            {
                sqlStr = "Select t.id,t.立项号,t.项目名称,t.建设单位,t.项目地址,t.征收部门 From zs_project t where t.补偿提交状态=1 and t.冻结审批状态=1 and t.补偿审批状态=1" + strsql + strwhere + "";
            }
            if (temp.IndexOf("房屋征收决定申请") > -1)
            {
                sqlStr = @"Select t1.ID,t.立项号,t.项目名称,t.征收实施单位,t1.征收许可证号 征收决定号,t1.征收评估机构,t.征收部门 
 From zs_project t,zs_prono t1 Where t.id=t1.pid and t.补偿实施审批状态=1 and t1.提交状态=1 and t1.审核状态=1 " + strsql + strwhere + "";
                if (temp.IndexOf("(区县)") > -1)
                {
                    sqlStr = @"Select t1.ID,t.立项号,t.项目名称,t.征收实施单位,t1.征收许可证号 征收决定号,t1.征收评估机构,t.征收部门 
 From zs_project t,zs_prono t1 Where t.id=t1.pid and t.补偿实施审批状态=1 and t1.提交状态=1 and t1.审核状态=1 
 and t.区属 in (" + System.Configuration.ConfigurationSettings.AppSettings["IsSQLXPro"].ToString() + ") " + strsql + strwhere;
                }
            }
            if (temp.IndexOf("房屋征收补偿决定审批") > -1)
            {
                sqlStr = @"Select Rownum 序号, Id, 产权人, 承租人, 证件号, 房屋坐落 From Zs_Zsperson Where " + strpsql + @" and Id Not In ((Select Personid From Zs_Hbhxy) Union
(Select Zspid From Zs_Proloupan Where Status = 1 And Zspid Is Not Null And Id In (Select Fid From Zs_Cqzhxy))) and id in (select zspid from zs_zzbcjfenhu ) " + strwhere + "";
            }
            if (temp == "xzxmzj")
            {
                sqlStr = @"select t.ID,t.立项号,t.项目名称,t.征收部门,t.征收实施单位  
from zs_project t where t.立项号 is not null " + strwhere;
            }
            if (temp == "房屋征收补偿资金监管")
            {
                sqlStr = @"select t.id ,
(select 立项号 from zs_project where id=t.pnid) 立项号,
t.征收实施单位,
t.开户银行,
t.存折金额,
t.银行账号,
t.余额
from ZS_BCBASICINFO t where t.开户审核状态=1 " + strwhere;
            }
            if (temp == "xzxmx")
            {
                string sqlWhere = string.Empty;
                if (!string.IsNullOrEmpty(qs))
                {
                    if (qs == "5")
                    {
                        sqlWhere = " and 区属 in ('秦淮区','白下区')";
                    }
                    else if (qs == "2")
                    {
                        sqlWhere = " and 区属 in ('鼓楼区','下关区')";
                    }
                    else
                    {
                        string mysql = @"select keyname from zs_params where name='区属' and keyvalue='" + qs + "'";
                        string strQS = Outida.GetValue(mysql);
                        if (!string.IsNullOrEmpty(strQS)) { sqlWhere = " and 区属 ='" + strQS + "'"; }
                    }
                }
                sqlStr = string.Format(@"select t1.ID,t1.征收许可证号 征收决定号,t.征收部门,t.征收实施单位,t.项目地址,t.项目名称,
nvl((select 权证户数 from zs_bcfaqs where 补偿实施=0 and pid=t.id),0) 居民户数,
nvl((select 工企单位 from zs_bcfaqs where 补偿实施=0 and pid=t.id),0) 工企单位,
nvl((Select Round((to_number(nvl(房屋建筑面积,0))/10000),2) from zs_bcfaqs where 补偿实施=0 and pid=t.id),0) 房屋建筑面积,
(select 征收签约期限 from zs_bcfaqs where 补偿实施=0 and pid=t.id) 征收期限 
 from zs_project t,zs_prono t1 where t1.pid=t.id {0} {1} and t1.审核状态=1 ", sqlWhere, strwhere);
            }
            if (temp == "xzxc")
            {
                sqlStr = @"select t.ID,t.立项号,t.项目名称,t.征收实施单位,t1.征收许可证号 征收决定号,t1.征收评估机构 
from zs_project t,zs_prono t1 where t1.pid=t.id and t1.审核状态=1 " + strwhere;
            }
            if (temp == "khyh")
            {
                sqlStr = @"select z.ID,z.单位名称,z.单位编号,z.地址 from zs_company z where z.单位类别='银行机构' ";
            }
            if (temp == "jftz")
            {
                sqlStr = @"select t1.id,t.立项号,t.项目名称,t.建设单位,t.项目地址,t1.征收许可证号 征收决定号 
from zs_project t,zs_prono t1 where t.id=t1.pid and t1.审核状态=1 " + strwhere;
            }
            if (temp == "sfxx")
            {
                sqlStr = @"select t1.id,t.立项号,t.项目名称,to_char(t.addtime,'yyyy-mm-dd') 征收时间,t.项目地址,t1.征收许可证号 征收决定号,t.立项号 
from zs_project t,zs_prono t1 where t.id=t1.pid and t1.审核状态=1 " + strwhere;
            }
            if (temp == "xfzdry")
            {
                sqlStr = @"select t1.id,t.项目名称,t.项目地址,t1.拆迁期限 征收期限,t1.征收许可证号 征收决定号,t1.实施单位联系人 征收人,
t.征收实施单位,t.评估机构,t.区属,t.立项号 from zs_project t,zs_prono t1 where t.id=t1.pid and t1.审核状态=1 " + strwhere;
            }
            if (string.IsNullOrEmpty(sqlStr))
            {
                sqlStr = @"select t.id,t.立项号,t.项目名称,t.建设单位,t.项目地址 from zs_project t where t.id in 
(select pid from zs_prono where 审核状态=1) " + strwhere;
            }
            DataTable dt = null;
            Outida.RunSql(sqlStr, out dt);
            return dt;
        }
        //        public override DataTable GetSelPeople(string strwhere)
        //        {
        //            string sqlStr = @"select * from (select id,
        //(select id from zs_zsperson where id=t.zspid) personid,
        //(select 产权人 from zs_zsperson where id=t.zspid) 产权人,
        //(select 丘权号 from zs_zsperson where id=t.zspid) 丘权号,
        //(select 房屋坐落 from zs_zsperson where id=t.zspid) 房屋坐落,
        //(select 征收许可证号
        //from zs_prono where id =t.pid)征收许可证号,
        //(select 区属
        //from zs_project
        //where id in (select pid from zs_prono where id = t.pid)) 区属,  
        //( select 项目名称 from zs_project where id in(
        //select pid
        //from zs_prono where id =t.pid))项目名称,
        //( select 立项号 from zs_project where id in(
        //select pid
        //from zs_prono where id =t.pid))立项号
        //from zs_zzbcjfenhu t ) where 1=1 
        //and personid not in 
        //(select id from zs_zsperson where id in (select zspid from zs_zzbcjfenhu where id 
        //in (select 关联ID from xt_instance_ext where iid in (select iid from st_instance where STATUS<>'-2')))) 
        //" + strwhere;

        //            DataTable dt = null;
        //            Outida.RunSql(sqlStr, out dt);
        //            return dt;
        //        }


        public override DataTable GetSelPeople(string strwhere)
        {
            string sqlStr = @"select id,
       t.zspid personid,
       (select 产权人 from zs_zsperson where id = t.zspid) 产权人,
       (select 丘权号 from zs_zsperson where id = t.zspid) 丘权号,
       (select 房屋坐落 from zs_zsperson where id = t.zspid) 房屋坐落,
       (select 征收许可证号 from zs_prono where id = t.pid) 征收许可证号,
       (select 区属 from zs_project where id in (select pid from zs_prono where id = t.pid)) 区属,
       (select 项目名称 from zs_project where id in (select pid from zs_prono where id = t.pid)) 项目名称,
       (select 立项号 from zs_project where id in (select pid from zs_prono where id = t.pid)) 立项号
  from zs_zzbcjfenhu t
 where 1 = 1
   and zspid not in (select zspid from zs_zzbcjfenhu z,xt_instance_ext x ,st_instance s 
         where z.id=x.关联id and x.iid=s.iid and s.status <> -2) 
        " + strwhere;

            DataTable dt = null;
            Outida.RunSql(sqlStr, out dt);
            return dt;
        }
        public override DataTable GetSelPeople1(string strwhere)
        {
            string sqlStr = @"select id,
       t.zspid personid,
       (select 产权人 from zs_zsperson where id = t.zspid) 产权人,
       (select 丘权号 from zs_zsperson where id = t.zspid) 丘权号,
       (select 房屋坐落 from zs_zsperson where id = t.zspid) 房屋坐落,
       (select 征收许可证号 from zs_prono where id = t.pid) 征收许可证号,
       (select 区属 from zs_project where id in (select pid from zs_prono where id = t.pid)) 区属,
       (select 项目名称 from zs_project where id in (select pid from zs_prono where id = t.pid)) 项目名称,
       (select 立项号 from zs_project where id in (select pid from zs_prono where id = t.pid)) 立项号
  from zs_zzbcjfenhu t
 where 1 = 1
   and zspid not in (select zspid from zs_zzbcjfenhu z,xt_instance_ext x ,st_instance s 
         where z.id=x.关联id and x.iid=s.iid and s.status <> -2) 
        " + strwhere;

            DataTable dt = null;
            Outida.RunSql(sqlStr, out dt);
            return dt;
        }
        public override Bcbasicinfo GetBcbasicinfo(string strid)
        {
            Bcbasicinfo bcbasicinfo = new Bcbasicinfo();
            string sql = "select * from zs_bcbasicinfo where id='" + strid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return bcbasicinfo = DataHelper.DataTableToTOne<Bcbasicinfo>(dt);
        }
        public override bool SaveBcbasicinfo(string strid, Bcbasicinfo bcbasicinfo)
        {
            string sql = string.Empty;
            if (!string.IsNullOrEmpty(strid))
            {
                sql = DataHelper.UpdateT<Bcbasicinfo>(bcbasicinfo, "ZS_BCBASICINFO", new string[] { "id" });
            }
            else
            {
                sql = DataHelper.AddT<Bcbasicinfo>(bcbasicinfo, "ZS_BCBASICINFO");
            }
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override string GetISDPCHZ(string pid)//获取是否已确认划转的数据
        {
            string sql = @"select count(*) num
from zs_zspch t where t.提交状态='2' and t.pnid in (
select id from zs_prono where pid='" + pid + "') ";
            return Outida.GetValue(sql);
        }
        public override string GetISPCHZ(string pid)
        {
            string sql = @"select count(*) num
from zs_zspch t where t.提交状态=1 and t.pnid in (
select id from zs_prono where pid='" + pid + "') ";
            return Outida.GetValue(sql);
        }
        public override DataTable GetDZJE(string bid)
        {
            string sql = @"select ID,到账金额,备注,(case when 是否首期='0' then '否' when 是否首期='1' then '是' else null end) 是否首期,到账时间,
(select name from zs_user where id=t.urid) 操作员 from ZS_NEW_MONEY t where bid='" + bid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override string GetNowYE(string bid)
        {
            string sql = @"select (select (to_number(nvl((select sum(to_number(nvl(replace(到账金额, '\', ''), 0))) from zs_new_money
 where bid = p.id),0)) -
       to_number(nvl((select sum(to_number(nvl(replace(划转金额, '\', ''), 0))) from zs_change_money
 where bid = p.id),0))) from dual) 余额  from zs_bcbasicinfo p where p.id='" + bid + "'";
            return Outida.GetValue(sql);
        }
        public override NEW_MONEY GetNewmoney(string nid)
        {
            NEW_MONEY newmoney = new NEW_MONEY();
            string sql = "select * from zs_new_money where id='" + nid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return newmoney = DataHelper.DataTableToTOne<NEW_MONEY>(dt);
        }
        public override bool SaveNewmoney(string nid, NEW_MONEY newmoney)
        {
            string sql = string.Empty;
            if (!string.IsNullOrEmpty(nid))
            {
                sql = DataHelper.UpdateT<NEW_MONEY>(newmoney, "zs_new_money", new string[] { "id" });
            }
            else
            {
                sql = DataHelper.AddT<NEW_MONEY>(newmoney, "zs_new_money");
            }
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override bool GetNewMoneyISFirst(string bid)
        {
            string sql = @"select * from ZS_NEW_MONEY where bid='" + bid + "' and 是否首期=1";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt.Rows.Count > 0 ? true : false;
        }
        public override bool GetNewMoneyNowIFT(string strid, string bid)
        {
            string sql = @"select * from ZS_NEW_MONEY where id='" + strid + "' and bid='" + bid + "' and 是否首期=1";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt.Rows.Count > 0 ? true : false;
        }
        public override DataTable GetHZJE(string pid, string strwhere)
        {
            string sql = @"select distinct t.征收批次号,t.PCID,t.PNID,
(select 立项号 from zs_project where id in 
(select pid from zs_prono where id=t.pnid) ) 立项号,
(select 项目名称 from zs_project where id in 
(select pid from zs_prono where id=t.pnid) ) 项目名称,
(select 征收许可证号 征收决定号 from zs_prono where id=t.pnid) 征收决定号,
(select id from zs_change_money where pcid=t.pcid ) cmid 
from zs_zspch t where t.提交状态=2 and t.pnid in (
select id from zs_prono where pid='" + pid + "') ";
            if (!string.IsNullOrEmpty(strwhere)) { sql += strwhere; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetDHZJE(string strwhere)
        {
            string sql = @"select distinct t.征收批次号,t.PCID,t.PNID,t.提交状态,
(select 立项号 from zs_project where id in 
(select pid from zs_prono where id=t.pnid) ) 立项号,
(select 项目名称 from zs_project where id in 
(select pid from zs_prono where id=t.pnid) ) 项目名称,
(select 征收许可证号 征收决定号 from zs_prono where id=t.pnid) 征收决定号,
(select id from zs_change_money where pcid=t.pcid ) cmid 
from zs_zspch t where t.提交状态=1 ";
            if (!string.IsNullOrEmpty(strwhere)) { sql += strwhere; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override CHANGE_MONEY GetCHANGEMONEY(string strid)
        {
            CHANGE_MONEY changemoney = new CHANGE_MONEY();
            string sql = "select * from ZS_CHANGE_MONEY where id='" + strid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return changemoney = DataHelper.DataTableToTOne<CHANGE_MONEY>(dt);
        }
        public override bool SaveChangemoney(string nid, CHANGE_MONEY changemoney)
        {
            string sql = string.Empty;
            if (!string.IsNullOrEmpty(nid))
            {
                sql = DataHelper.UpdateT<CHANGE_MONEY>(changemoney, "ZS_CHANGE_MONEY", new string[] { "id" });
            }
            else
            {
                sql = DataHelper.AddT<CHANGE_MONEY>(changemoney, "ZS_CHANGE_MONEY");
            }
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override DataTable GetHZJERY(string pcid, string strwhere)
        {
            string sql = string.Format(@"select t.产权人,t.证件号,t.承租人,t.房屋坐落,t0.id,t.id personid,t0.pnid,t0.pcid, 
(select sum((to_number(nvl(replace(ZZ补偿金额合计,'\',''),0))+to_number(nvl(replace(YY评估补偿金额,'\',''),0))
  +to_number(nvl(replace(FYY评估补偿金额,'\',''),0))+to_number(nvl(replace(ZS室内装饰装修评估补偿金额合计,'\',''),0)))) 
  from zs_zzbcjfenhu where zspid=t.id) 补偿总金额,
(select to_number(nvl(replace(t1.textbox26,'\',''),0))+to_number(nvl(replace(t1.textbox27,'\',''),0))+
       to_number(nvl(replace(t1.textbox28,'\',''),0))+to_number(nvl(replace(t1.textbox28,'\',''),0))+
       to_number(nvl(replace(t1.textbox30,'\',''),0))+to_number(nvl(replace(t1.textbox31,'\',''),0))+
       to_number(nvl(replace(t1.textbox32,'\',''),0))+to_number(nvl(replace(t1.textbox33,'\',''),0))+
       to_number(nvl(replace(t1.textbox34,'\',''),0))+to_number(nvl(replace(t1.textbox35,'\',''),0))+
       to_number(nvl(replace(t1.textbox42,'\',''),0))+to_number(nvl(replace(t1.textbox43,'\',''),0))+
       to_number(nvl(replace(t1.textbox44,'\',''),0))+to_number(nvl(replace(t1.textbox45,'\',''),0))+
       to_number(nvl(replace(t1.textbox46,'\',''),0))+to_number(nvl(replace(t1.textbox166,'\',''),0))
 from zs_cqzhxy t1 where t1.fid in (
select id from zs_proloupan where zspid=t.id)) 产权补助,
(select to_number(nvl(replace(t2.textbox24,'\',''),0))+to_number(nvl(replace(t2.textbox25,'\',''),0))+
       to_number(nvl(replace(t2.textbox26,'\',''),0))+to_number(nvl(replace(t2.textbox27,'\',''),0))+
       to_number(nvl(replace(t2.textbox28,'\',''),0))+to_number(nvl(replace(t2.textbox29,'\',''),0))+
       to_number(nvl(replace(t2.textbox30,'\',''),0))+to_number(nvl(replace(t2.textbox31,'\',''),0))+
       to_number(nvl(replace(t2.textbox32,'\',''),0))+to_number(nvl(replace(t2.textbox33,'\',''),0))+
       to_number(nvl(replace(t2.textbox34,'\',''),0))+to_number(nvl(replace(t2.textbox41,'\',''),0))+
       to_number(nvl(replace(t2.textbox42,'\',''),0))+to_number(nvl(replace(t2.textbox43,'\',''),0))+
       to_number(nvl(replace(t2.textbox44,'\',''),0))+to_number(nvl(replace(t2.textbox45,'\',''),0)) 
 from zs_hbhxy t2 where t2.personid =t.id) 货币化补助,t0.划转金额,
 (select sum(to_number(nvl(replace(划转金额,'\',''),0))) from zs_zspch where zsuserid=t.id) 已划转金额,t0.提交状态  
   from zs_zsperson t,ZS_ZSPCH t0 where t.id=t0.zsuserid and t0.pcid='{0}' ", pcid);
            if (string.IsNullOrEmpty(strwhere)) { sql += strwhere; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetHZDZTJ(string type, string strwhere)
        {
            string sql = string.Empty;
            if (type == "0")
            {
                sql = @"select t.时间 划转时间,sum(nvl(replace(t.划转金额, '\', ''),0.0)) 划转金额 from ZS_BCBASICINFO t 
where t.时间 is not null " + strwhere + " group by t.时间 order by t.时间 desc  ";
            }
            else if (type == "1")
            {
                sql = @"select t.时间 到账时间,sum(nvl(replace(t.到账金额, '\', ''),0.0)) 到账金额 from ZS_BCBASICINFO t 
where t.时间 is not null " + strwhere + " group by t.时间 order by t.时间 desc  ";
            }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetZJYH(string strwhere)
        {
            string sql = @"select distinct 开户银行 银行名称 from ZS_BCBASICINFO where 1=1  ";
            if (!string.IsNullOrEmpty(strwhere)) { sql += strwhere; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override string GetYHProNum(string temp)
        {
            string sql = @"select count(*) 项目个数 from zs_project where id in (
select PNID from ZS_BCBASICINFO where 开户银行='" + temp + "')";
            return Outida.GetValue(sql);
        }
        public override DataTable GetKHCompanyHZ(string strwhere, string strwhere1)
        {
            string sql = string.Format(@"select distinct p1.区属,
       (select count(*) from zs_bcbasicinfo where pnid in (select id from zs_project where 区属=p1.区属)) 单位数,
       (select sum(to_number(nvl(replace(划转金额, '\', ''),0))) from zs_bcbasicinfo 
       where pnid in (select id from zs_project where 区属=p1.区属)) 划转金额,
       (select sum(to_number(nvl(replace(余额, '\', ''),0))) from zs_bcbasicinfo 
       where pnid in (select id from zs_project where 区属=p1.区属)) 余额
  from zs_project p1 where p1.id in (select pnid from zs_bcbasicinfo where 1=1 {0}) {1}", strwhere, strwhere1);
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetProList(string column, string strwhere)
        {
            string sql = @"select t.ID,t.项目名称,t.建设单位,t.立项号,t.征收范围,t.概算评估机构,t.部门申请状态 from zs_project t where t.审批状态=1 ";
            if (!string.IsNullOrEmpty(column))
            {
                sql = string.Format(@"select t.ID,t.项目名称,t.建设单位,t.立项号,t.征收范围,
t.概算评估机构 {0} from zs_project t where t.审批状态=1 ", column);
            }
            if (!string.IsNullOrEmpty(strwhere)) { sql += strwhere; }
            sql += " order by t.立项号 desc";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override bool SaveGSPGJG(string strid, Project project)
        {
            string sql = string.Empty;
            if (!string.IsNullOrEmpty(strid))
            {
                sql = DataHelper.UpdateT<Project>(project, "ZS_PROJECT", new string[] { "id" });
            }
            else
            {
                sql = DataHelper.AddT<Project>(project, "ZS_PROJECT");
            }
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override bool SaveZSPGJG(string strid, Prono prono)
        {
            string sql = string.Empty;
            if (!string.IsNullOrEmpty(strid))
            {
                sql = DataHelper.UpdateT<Prono>(prono, "ZS_PRONO", new string[] { "id" });
            }
            else
            {
                sql = DataHelper.AddT<Prono>(prono, "ZS_PRONO");
            }
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override DataTable GetPGJGBM(string strwhere)
        {
            string sql = @"select t1.ID,t.立项号,t.项目名称,t.建设单位,t.征收范围,t1.征收许可证号 征收决定号,t.id pid
 from zs_project t,zs_prono t1 where t.id=t1.pid and t1.审核状态=1 and t1.id in (select pnid from zs_pgfbsq where 提交状态 =1 ) ";
            if (!string.IsNullOrEmpty(strwhere)) { sql += strwhere; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetPGJGBMXX(string tt, string pnid, string strwhere1, string strwhere2)
        {
            string sql = string.Empty;
            if (tt == "1")
            {
                sql = string.Format(@"select t.id,t.pnid,t.申请单位,t.经办人,to_char(t.申请时间,'yyyy-mm-dd') 申请时间,
(select 立项号 from zs_project where id in (select pid from zs_prono where id=t.pnid)) 立项号,
(select 项目名称 from zs_project where id in (select pid from zs_prono where id=t.pnid)) 项目名称,
t.提交状态,to_number(nvl((select distinct 报名状态 from ZS_PGBMJL where sid=t.id {0}),0)) 报名状态,
to_number(nvl((select distinct 通过状态 from ZS_PGBMJL where sid in t.id {0}),0)) 通过状态,
(select id bmid from ZS_PGBMFBXX where sid=t.id) bmid   
from zs_pgfbsq t where 提交状态=1 and pnid ='{1}'", strwhere1, pnid);
            }
            else if (tt == "0")
            {
                sql = string.Format(@"select t.id,t.pnid,t.申请单位,t.经办人,to_char(t.申请时间,'yyyy-mm-dd') 申请时间,
(select 立项号 from zs_project where id in (select pid from zs_prono where id=t.pnid)) 立项号,
(select 项目名称 from zs_project where id in (select pid from zs_prono where id=t.pnid)) 项目名称,
t.提交状态,to_number(nvl((select distinct 报名状态 from ZS_PGBMJL where sid=t.id {0}),0)) 报名状态,
to_number(nvl((select distinct 通过状态 from ZS_PGBMJL where sid in t.id {0}),0)) 通过状态,
(select id bmid from ZS_PGBMFBXX where sid=t.id) bmid   
from zs_pgfbsq t where pnid ='{1}'", strwhere1, pnid);
            }
            if (!string.IsNullOrEmpty(strwhere2)) { sql += strwhere2; }
            sql += " order by t.申请时间 desc";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override void InsertPGJGBMXX(string strPNID, string strCID, string SID)
        {
            string strTime = System.DateTime.Now.ToString();
            if (!string.IsNullOrEmpty(strCID))
            {
                string sql = string.Format(@"insert into ZS_PGBMJL (PNID,COMID,报名时间,SID,报名状态) values 
('{0}','{1}',to_date('{2}','yyyy-MM-dd hh24-mi-ss'),'{3}','1')", strPNID, strCID, strTime, SID);
                Outida.RunSql(sql);
            }
        }
        public override void UpdatePGJGNull(string tablename, string columname, string strid)
        {
            string sql = "update " + tablename + " set " + columname + "=null where id ='" + strid + "'";
            Outida.RunSql(sql);
        }
        public override Pgbmfbxx GetPgbmfbxx(string strid)
        {
            Pgbmfbxx pgbmfbxx = new Pgbmfbxx();
            string sql = "select * from zs_pgbmfbxx t where id='" + strid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return pgbmfbxx = DataHelper.DataTableToTOne<Pgbmfbxx>(dt);
        }
        public override bool SavePGJGBMXX(string strid, Pgbmfbxx pgbmfbxx)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(strid))
            {
                sql = DataHelper.AddT<Pgbmfbxx>(pgbmfbxx, "Zs_Pgbmfbxx");
            }
            else
            {
                sql = DataHelper.UpdateT<Pgbmfbxx>(pgbmfbxx, "Zs_Pgbmfbxx", new string[] { "id" });
            }
            return Outida.RunSql(sql) > 0 ? true : false;
        }

        
        public override DataTable GetProUnList1(string column, string sqlwhere)
        {
            string sql = string.Format(@"Select t.Id,
       t.立项号,
       t.项目名称,
       t.建设单位,
       t.征收范围,
       (Select 征收许可证号 From Zs_Prono p Where p.Pid = t.Id) 征收决定号,
       (Select 征收评估机构 From Zs_Prono p Where p.Pid = t.Id) 征收评估机构,
       (Case When 部门申请状态 = '0' Then '未申请' Else '已申请' End ) 申请状态, t.Id Pid {0}
  From Zs_Project t
 Where 1 = 1
", column);
            if (!string.IsNullOrEmpty(sqlwhere)) { sql += sqlwhere; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }


        public override DataTable GetProUnList(string column, string sqlwhere)
        {
            string sql = string.Format(@"select t1.ID,t.立项号,t.项目名称,t.建设单位,t.征收范围,t1.征收许可证号 征收决定号,
t.id pid,t1.已通过征收评估机构 征收评估机构 {0}
 from zs_project t,zs_prono t1 where t.id=t1.pid and t1.审核状态=1 ", column);
            if (!string.IsNullOrEmpty(sqlwhere)) { sql += sqlwhere; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override bool CkSomStr(string tablename, string cloumn, string newvalue)
        {
            string sql = @"select * from " + tablename + " where " + cloumn + "='" + newvalue + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            if (dt.Rows.Count > 0) { return true; } else { return false; }
        }
        public override bool SavePgfbsq(string strid, PGFBSQ pgfbsq)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(strid))
            {
                sql = DataHelper.AddT<PGFBSQ>(pgfbsq, "ZS_PGFBSQ");
            }
            else
            {
                sql = DataHelper.UpdateT<PGFBSQ>(pgfbsq, "ZS_PGFBSQ", new string[] { "id" });
            }
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override DataTable GetPGTZXX(string pnid, string sid, string strCName)
        {
            string sql = string.Format(@"select t.id,t.审批状态,t.报名时间,
(select 单位名称 from zs_company where id =t.comid) 单位名称     
from ZS_PGBMJL t where t.pnid ='{0}' and t.sid='{1}'", pnid, sid);
            if (!string.IsNullOrEmpty(strCName))
            {
                sql += " and t.comid in (select id from zs_company where 单位名称 like '%" + strCName + "%') ";
            }
            sql += " order by t.报名时间 asc";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetZZBCJHZ(string strwhere)
        {
            string sql = @"select p.征收评估机构,
       z.房屋坐落,
       z.丘权号 丘号,
       z.产权人,
       z.承租人,
       z.权证性质,
       z.权号 权证号,
       z.房屋建筑面积 分户建筑面积,
       z.土地分摊面积 地大于房土地面积,
       (select keyname from zs_params where name='结构等级' and keyvalue=z.房屋结构) 房屋建筑结构,
       z.所属层次 所在层数,
       t.zz基准价格 基准价格,
       t.zz评估单价 评估单价,
       t.zz评估总价 评估总价,
       t.zz补偿金额合计 补偿金额合计,
       t.zz产权人补偿款 产权人补偿款,
       t.zz承租人补偿款 承租人补偿款,
       t.zz备注 备注,t.id,t.id 编号
 from zs_zzbcjfenhu t,ZS_ZSPERSON z,zs_prono p where t.zspid=z.id 
 and z.房屋用途='1' and t.zz补偿金额合计 is not Null
 And p.id = t.pid";
            if (!string.IsNullOrEmpty(strwhere)) { sql += strwhere; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetFZZBCJHZ(string strwhere)
        {
            string sql = string.Format(@"select * from 
(select z.id 编号,z.房屋坐落,
       z.丘权号 丘号,
       z.产权人,
       z.承租人,
       z.权证性质,
       z.权号 权证号,
       z.房屋建筑面积 分户建筑面积,
       z.土地分摊面积 地大于房土地面积,
       (select keyname from zs_params where name='房屋用途' and keyvalue=z.房屋用途) 房屋用途,
       z.所属类别,
       (select keyname from zs_params where name='结构等级' and keyvalue=z.房屋结构) 房屋建筑结构,
       z.所属层次 所在层数,
       t.yy评估单价 评估单价,
       t.yy评估总价 评估总价,
       t.yy评估补偿金额 补偿金额合计,
       t.yy产权人补偿款 产权人补偿款,
       t.yy承租人补偿款 承租人补偿款,
       t.yy备注 备注,t.id
 from zs_zzbcjfenhu t,ZS_ZSPERSON z where t.zspid=z.id
 and z.房屋用途 not in ('1') and t.yy评估补偿金额 is not null {0} )
union all
(select z.id 编号,z.房屋坐落,
       z.丘权号 丘号,
       z.产权人,
       z.承租人,
       z.权证性质,
       z.权号 权证号,
       z.房屋建筑面积 分户建筑面积,
       z.土地分摊面积 地大于房土地面积,
       (select keyname from zs_params where name='房屋用途' and keyvalue=z.房屋用途) 房屋用途,
       z.所属类别,
       (select keyname from zs_params where name='结构等级' and keyvalue=z.房屋结构) 房屋建筑结构,
       z.所属层次 所在层数,
       t.fyy评估单价 评估单价,
       t.fyy评估总价 评估总价,
       t.fyy评估补偿金额 补偿金额合计,
       t.fyy产权人补偿款 产权人补偿款,
       t.fyy承租人补偿款 承租人补偿款,
       t.fyy备注 备注,t.id
 from zs_zzbcjfenhu t,ZS_ZSPERSON z where t.zspid=z.id
 and z.房屋用途 not in ('1') and t.fyy评估补偿金额 is not null {0}) ", strwhere);
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetPGFHXX(string type, string strwhere)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(type)) { type = "0"; }
            if (type == "0")//住宅评估
            {
                sql = @"select rownum 序号,p.产权人,p.承租人,p.房屋坐落,p.房屋建筑面积 面积,z.ZZ产权人补偿款,z.ZZ承租人补偿款,z.ZZ评估总价,
(select 区属 from zs_project where id in (select pid from zs_prono where id=z.pid)) 区属,z.id,z.zspid from ZS_ZZBCJFENHU z,
ZS_ZSPERSON p where z.zspid=p.id and z.评估分户类型=0 ";
            }
            else if (type == "1")//营业性评估
            {
                sql = @"select rownum 序号,p.产权人,p.承租人,p.房屋坐落,p.房屋建筑面积 面积,z.YY产权人补偿款,z.YY承租人补偿款,z.YY评估总价,
(select 区属 from zs_project where id in (select pid from zs_prono where id=z.pid)) 区属,z.id,z.zspid from ZS_ZZBCJFENHU z,ZS_ZSPERSON p where z.zspid=p.id and z.评估分户类型=1 ";
            }
            else if (type == "2")//非营业性评估
            {
                sql = @"select rownum 序号,p.产权人,p.承租人,p.房屋坐落,p.房屋建筑面积 面积,
z.FYY产权人补偿款,z.FYY承租人补偿款,z.FYY评估总价,
(select 区属 from zs_project where id in (select pid from zs_prono where id=z.pid)) 区属,z.id,z.ZSPID  
 from ZS_ZZBCJFENHU z,ZS_ZSPERSON p where z.zspid=p.id and z.评估分户类型=2 ";
            }
            else if (type == "3")//装修评估
            {
                sql = @"select rownum 序号,p.产权人,p.承租人,p.房屋坐落,p.房屋建筑面积 面积,
z.ZS产权人补偿款,z.ZS承租人补偿款,z.ZS室内装饰装修评估补偿金额合计,
(select 区属 from zs_project where id in (select pid from zs_prono where id=z.pid)) 区属,z.id,z.ZSPID  
 from ZS_ZZBCJFENHU z,ZS_ZSPERSON p where z.zspid=p.id and z.评估分户类型=3 ";
            }
            else if (type == "4") {//住改非评估
                sql = @"select rownum 序号,p.产权人,p.承租人,p.房屋坐落,p.房屋建筑面积 面积,z.ZGF产权人补偿款,z.ZGF承租人补偿款,z.ZGF评估总价,
(select 区属 from zs_project where id in (select pid from zs_prono where id=z.pid)) 区属,z.id,z.zspid from ZS_ZZBCJFENHU z,
ZS_ZSPERSON p where z.zspid=p.id and z.评估分户类型=4 ";
            }
            if (!string.IsNullOrEmpty(strwhere)) { sql += strwhere; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override ADDAREA GetADDAREA(string strid)
        {
            ADDAREA addarea = new ADDAREA();
            string sql = "select * from zs_addarea where id='" + strid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return addarea = DataHelper.DataTableToTOne<ADDAREA>(dt);
        }
        public override List<ADDAREA> GetAddareaList(string strwhere)
        {
            List<ADDAREA> addarealist = new List<ADDAREA>();
            string sql = "select * from zs_addarea where 1=1 ";
            if (!string.IsNullOrEmpty(strwhere)) { sql += strwhere; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            addarealist = DataHelper.DataTableToT<ADDAREA>(dt);
            return addarealist;
        }
        public override bool SaveAddarea(string strid, ADDAREA addarea)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(strid))
            {
                sql = DataHelper.AddT<ADDAREA>(addarea, "ZS_ADDAREA");
            }
            else
            {
                sql = DataHelper.UpdateT<ADDAREA>(addarea, "ZS_ADDAREA", new string[] { "id" });
            }
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override PGGS GetPGGS(string pnid)
        {
            PGGS pggs = new PGGS();
            string sql = "select * from zs_pggs where pid ='" + pnid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return pggs = DataHelper.DataTableToTOne<PGGS>(dt);
        }
        public override PGJGHZ GetPGJGHZ(string pnid)
        {
            PGJGHZ pgjghz = new PGJGHZ();
            string sql = "select * from zs_pgjghz where pid ='" + pnid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return pgjghz = DataHelper.DataTableToTOne<PGJGHZ>(dt);
        }
        public override ZZBCJFENHU GetZZBCJFENHU(string pnid)
        {
            ZZBCJFENHU zzbcjfenhu = new ZZBCJFENHU();
            string sql = "select * from zs_ZZBCJFENHU where pid ='" + pnid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return zzbcjfenhu = DataHelper.DataTableToTOne<ZZBCJFENHU>(dt);
        }
        public override ZSPERSON GetZSPERSON(string personid)
        {
            ZSPERSON zsperson = new ZSPERSON();
            string sql = "select * from zs_ZSPERSON where id='" + personid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return zsperson = DataHelper.DataTableToTOne<ZSPERSON>(dt);
        }
        public override Cqzhxy GetCqzhxy(string strid)
        {
            Cqzhxy cqzhxy = new Cqzhxy();
            string sql = "select * from zs_cqzhxy where id='" + strid + "' ";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return cqzhxy = DataHelper.DataTableToTOne<Cqzhxy>(dt);
        }
        public override DataTable GetHBHXY(string pid, string strwhere)
        {
            string sql = string.Format(@"select *
  from (select 
               z.产权人,
               z.承租人,
               z.证件号,
               z.房屋坐落,
               z.房屋建筑面积,z.丘权号,
               h.id,
               h.pnid,
               h.personid,h.消除异常,(zsjdh ||textbox1) 协议号,
               h.textbox1
          from zs_hbhxy h, ZS_ZSPERSON z
         where h.personid = z.id
           and h.PNID = '{0}' order by z.addtime desc) zh where 1=1 ", pid);
            if (!string.IsNullOrEmpty(strwhere)) { sql += strwhere; }
            sql += " Order By zh.textbox1 Desc";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override string GetQYFWJJ(string personid, string pid)
        {
            string sql = @"select sum(to_number(nvl(replace(ZZ评估总价, '\', ''),0))	+to_number(nvl(replace(YY评估总价, '\', ''),0))+to_number(nvl(replace(FYY评估总价, '\', ''),0))) 区域房屋均价 
from zs_zzbcjfenhu where zspid='" + personid + "' and pid ='" + pid + "'";
            return Outida.GetValue(sql);
        }
        public override string GetPGBCK(string personid, string pid, string type)
        {
            string sql = string.Empty;
            string temp = string.Empty;
            if (string.IsNullOrEmpty(type)) { type = "0"; }
            if (type == "0")
            {
                sql = @"select sum(to_number(nvl(replace(zz产权人补偿款, '\', ''), 0)) + to_number(nvl(replace(zz承租人补偿款, '\', ''), 0)) +
       to_number(nvl(replace(yy产权人补偿款, '\', ''), 0)) + to_number(nvl(replace(yy承租人补偿款, '\', ''), 0)) +
       to_number(nvl(replace(fyy产权人补偿款, '\', ''), 0)) + to_number(nvl(replace(fyy承租人补偿款, '\', ''), 0))) 补偿款,
       sum(to_number(nvl(replace(zs产权人补偿款, '\', ''), 0)) + to_number(nvl(replace(zs承租人补偿款, '\', ''), 0))) 装修款 
from zs_zzbcjfenhu where pid='" + pid + "' and zspid='" + personid + "'";
            }
            else if (type == "1")
            {
                sql = @"select to_number(nvl(replace(TEXTBOX19,'\',''),0)) 补偿款,
(to_number(nvl(replace(TEXTBOX40,'\',''),0))+
to_number(nvl(replace(TEXTBOX48,'\',''),0))+
to_number(nvl(replace(TEXTBOX51,'\',''),0))+
to_number(nvl(replace(TEXTBOX37,'\',''),0))) 装修款 from zs_hbhxy
where pnid='" + pid + "' and personid='" + personid + "'";
            }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            if (dt.Rows.Count > 0)
            {
                string pgbck = dt.Rows[0]["补偿款"].ToString();
                string pgzxk = dt.Rows[0]["装修款"].ToString();
                temp = pgbck + "," + pgzxk;
            }
            return temp;
        }
        public override Hbhxy GetHbhxy(string strid)
        {
            Hbhxy hbhxy = new Hbhxy();
            string sql = "select * from zs_hbhxy where id='" + strid + "' ";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return hbhxy = DataHelper.DataTableToTOne<Hbhxy>(dt);
        }
        public override bool SaveHbhxy(string strid, Hbhxy hbhxy)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(strid))
            {
                sql = DataHelper.AddT<Hbhxy>(hbhxy, "zs_hbhxy");
            }
            else
            {
                sql = DataHelper.UpdateT<Hbhxy>(hbhxy, "zs_hbhxy", new string[] { "id" });
            }
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override DataTable GetXZXYRY(string pnid, string strwhere)
        {
            string sql = @"select rownum 序号,id,产权人,承租人,
(select keyname from zs_params where name='房屋用途' and keyvalue=to_number(nvl(t.房屋用途,0))) 房屋用途,
证件号,房屋坐落 from zs_zsperson t where id in (select zspid from zs_zzbcjfenhu where pid = '" + pnid + "')";
            if (!string.IsNullOrEmpty(strwhere)) { sql += strwhere; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetSelectRY(string values, string pid, string type)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(type)) { type = "0"; }
            if (type == "0")
            {
                sql = string.Format(@" select z.产权人, z.证件号 from zs_hbhxy h, ZS_ZSPERSON z
  where h.personid = z.id and h.pnid in (select pid from zs_zzbcjfenhu)
    and h.PNID = '{0}' and z.id in ('{1}')", pid, values.Replace("|", "','"));
            }
            else if (type == "1")
            {
                sql = string.Format(@"select * from zs_zzbcjfenhu where zspid in('{0}')  and pid = '{1}' and zspid not in
  (select personid from zs_hbhxy where pnid = '{1}')", values.Replace("|", "','"), pid);
            }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetDYRYLH(string type, string strid)
        {
            string sql = @"select x.printtime,(select name from zs_user where id=x.userid) name from ZS_XYDYRY x where 1=1 ";
            if (!string.IsNullOrEmpty(type)) { sql += " and x.type='" + type + "'"; }
            if (!string.IsNullOrEmpty(strid)) { sql += " and x.xyid='" + strid + "'"; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override string GetBindFname(string fname)
        {
            string sql = @"Select fid From st_form t Where t.fname='" + fname + @"' And version=
        (Select Max(version) From st_form t Where t.fname='" + fname + @"')";
            return Outida.GetValue(sql);
        }
        public override DataTable GetYBBTJ(string strwhere)
        {
            string sql = @"select sum(to_number(nvl(replace(居民户数, '\', ''),0))) 居民户数,  
sum(to_number(nvl(replace(工企单位, '\', ''),0))) 工企单位  ,
sum(to_number(nvl(replace(征收总面积, '\', ''),0))) 征收总面积  ,
sum(to_number(nvl(replace(征收住宅总面积, '\', ''),0))) 征收住宅总面积 , 
sum(to_number(nvl(replace(已搬居民户数, '\', ''),0))) 已搬居民户数,  
sum(to_number(nvl(replace(已搬工企单位, '\', ''),0))) 已搬工企单位  ,
sum(to_number(nvl(replace(已搬面积, '\', ''),0))) 已搬面积  ,
sum(to_number(nvl(replace(已搬住宅面积, '\', ''),0))) 已搬住宅面积	,
sum(to_number(nvl(replace(已搬住房困难户数一, '\', ''),0))) 已搬住房困难户数一 , 
sum(to_number(nvl(replace(已搬住房困难户数二, '\', ''),0))) 已搬住房困难户数二  ,
sum(to_number(nvl(replace(未搬居民户数, '\', ''),0))) 未搬居民户数  ,
sum(to_number(nvl(replace(未搬工企单位, '\', ''),0))) 未搬工企单位  ,
sum(to_number(nvl(replace(未搬面积, '\', ''),0))) 未搬面积  ,
sum(to_number(nvl(replace(往年结转补偿决定案件数, '\', ''),0))) 往年结转补偿决定案件数  ,
sum(to_number(nvl(replace(往年结转案件建筑面积, '\', ''),0))) 往年结转案件建筑面积  ,
sum(to_number(nvl(replace(往年结转面积, '\', ''),0))) 往年结转面积  ,
sum(to_number(nvl(replace(本年度新受理补偿决定件数, '\', ''),0))) 本年度新受理补偿决定件数  ,
sum(to_number(nvl(replace(本年度新受理案件建筑面积, '\', ''),0))) 本年度新受理案件建筑面积  ,
sum(to_number(nvl(replace(本年度做出补偿决定件数, '\', ''),0))) 本年度做出补偿决定件数  ,
sum(to_number(nvl(replace(本年度做出案件建筑面积, '\', ''),0))) 本年度做出案件建筑面积  ,
sum(to_number(nvl(replace(强制征收户数, '\', ''),0))) 强制征收户数  ,
sum(to_number(nvl(replace(强制征收建筑面积, '\', ''),0))) 强制征收建筑面积,count(ID) sumno  
from zs_ybbtj y1 where 1=1 ";
            if (!string.IsNullOrEmpty(strwhere)) { sql += strwhere; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            if (!string.IsNullOrEmpty(dt.Rows[0]["sumno"].ToString()))
            {
                if (Convert.ToInt32(dt.Rows[0]["sumno"].ToString()) == 0)
                {
                    dt = null;
                }
            }
            return dt;
        }
        public override DataTable GetYBBBTQS()
        {
            string sql = @"select distinct qs from zs_ybbtj where 1=1 ";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetZSPCHXX(string pnid)
        {
            string sql = @"select distinct 征收批次号,pcid,pnid,首次,(select 征收许可证号 from zs_prono where id=p1.pnid) 征收决定号,提交状态 from zs_zspch p1 where 1=1 ";
            if (!string.IsNullOrEmpty(pnid))
            {
                sql += " and p1.pnid='" + pnid + "'";
            }
            sql += " order by substr(征收批次号,19,1)";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetBHZZJRY(string pnid, string pcid, string strwhere)
        {
            string sqlWhere = string.Empty;
            string cksql = string.Format(@"select zsuserid from zs_zspch where pcid='{0}' and pnid='{1}'", pcid, pnid);
            DataTable ckdt = null;
            Outida.RunSql(cksql, out ckdt);
            if (ckdt.Rows.Count > 0)
            {
                if (ckdt.Rows.Count == 1)
                {
                    string temp = ckdt.Rows[0]["zsuserid"].ToString();
                    if (!string.IsNullOrEmpty(temp))
                    {
                        sqlWhere = string.Format(@" and t.id not in ( select zsuserid from zs_zspch where pcid='{0}') ", pcid);
                    }
                }
                else
                {
                    sqlWhere = string.Format(@" and t.id not in ( select zsuserid from zs_zspch where pcid='{0}') ", pcid);
                }
            }
            string sql = string.Format(@"select t.id,t.产权人,t.承租人,t.证件号,t.房屋坐落,
(select sum((to_number(nvl(replace(ZZ补偿金额合计,'\',''), 0)) +
               to_number(nvl(replace(YY评估补偿金额,'\',''), 0)) +
               to_number(nvl(replace(FYY评估补偿金额,'\',''), 0)) +
               to_number(nvl(replace(ZS室内装饰装修评估补偿金额合计,'\',''), 0))))
          from zs_zzbcjfenhu
         where zspid = t.id) 补偿总金额,
  (select to_number(nvl(replace(t1.textbox26,'\',''),0))+to_number(nvl(replace(t1.textbox27,'\',''),0))+
       to_number(nvl(replace(t1.textbox28,'\',''),0))+to_number(nvl(replace(t1.textbox28,'\',''),0))+
       to_number(nvl(replace(t1.textbox30,'\',''),0))+to_number(nvl(replace(t1.textbox31,'\',''),0))+
       to_number(nvl(replace(t1.textbox32,'\',''),0))+to_number(nvl(replace(t1.textbox33,'\',''),0))+
       to_number(nvl(replace(t1.textbox34,'\',''),0))+to_number(nvl(replace(t1.textbox35,'\',''),0))+
       to_number(nvl(replace(t1.textbox42,'\',''),0))+to_number(nvl(replace(t1.textbox43,'\',''),0))+
       to_number(nvl(replace(t1.textbox44,'\',''),0))+to_number(nvl(replace(t1.textbox45,'\',''),0))+
       to_number(nvl(replace(t1.textbox46,'\',''),0))+to_number(nvl(replace(t1.textbox166,'\',''),0))
 from zs_cqzhxy t1 where t1.fid in (
select id from zs_proloupan where zspid=t.id)) 产权补助,
(select to_number(nvl(replace(t2.textbox24,'\',''),0))+to_number(nvl(replace(t2.textbox25,'\',''),0))+
       to_number(nvl(replace(t2.textbox26,'\',''),0))+to_number(nvl(replace(t2.textbox27,'\',''),0))+
       to_number(nvl(replace(t2.textbox28,'\',''),0))+to_number(nvl(replace(t2.textbox29,'\',''),0))+
       to_number(nvl(replace(t2.textbox30,'\',''),0))+to_number(nvl(replace(t2.textbox31,'\',''),0))+
       to_number(nvl(replace(t2.textbox32,'\',''),0))+to_number(nvl(replace(t2.textbox33,'\',''),0))+
       to_number(nvl(replace(t2.textbox34,'\',''),0))+to_number(nvl(replace(t2.textbox41,'\',''),0))+
       to_number(nvl(replace(t2.textbox42,'\',''),0))+to_number(nvl(replace(t2.textbox43,'\',''),0))+
       to_number(nvl(replace(t2.textbox44,'\',''),0))+to_number(nvl(replace(t2.textbox45,'\',''),0)) 
 from zs_hbhxy t2 where t2.personid =t.id) 货币化补助,
 (select sum(to_number(nvl(replace(划转金额,'\',''),0))) from zs_zspch where pnid ='{0}' and zsuserid =t.id) 划转金额,
  (select sum(to_number(nvl(replace(余额,'\',''),0))) from zs_zspch where pnid ='{0}' and zsuserid =t.id) 余额
   from zs_zsperson t where t.id in (
select personid from zs_hbhxy where pnid ='{0}' union 
(select zspid from zs_proloupan where id in (
select fid from zs_cqzhxy ) and pid in (select pid from zs_prono where id ='{0}'))) and t.id not in (
select zsuserid from zs_zspch where pnid='{0}' and replace(余额,'\','')=0) {1} ", pnid, sqlWhere);
            if (!string.IsNullOrEmpty(strwhere)) { sql += strwhere; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override void UpdatePCHRY(string values, string strPCID, string strPNID)
        {
            string strID = string.Empty;
            string ssql = string.Format(@"select id from zs_zspch where pcid='{0}' and pnid ='{1}'", strPCID, strPNID);
            DataTable sdt = null;
            Outida.RunSql(ssql, out sdt);
            if (sdt.Rows.Count == 1)
            {
                strID = sdt.Rows[0]["ID"].ToString();
            }
            string[] cTemp = values.Replace("|", ",").Split(',');
            for (int i = 0; i < cTemp.Length; i++)
            {
                string zsuserid = cTemp[i];
                string sql = string.Format(@"select zsuserid from zs_zspch where id='{0}'", strID);
                DataTable dt = null;
                Outida.RunSql(sql, out dt);
                if (dt.Rows.Count > 0)
                {
                    string iuid = dt.Rows[0]["zsuserid"].ToString();
                    if (string.IsNullOrEmpty(iuid))
                    {
                        string sql1 = string.Format(@"update zs_zspch set zsuserid='{0}' where id='{1}'", zsuserid, strID);
                        Outida.RunSql(sql1);
                    }
                }
                string sql4 = string.Format(@"select * from zs_zspch where zsuserid='{0}' and pcid='{1}' and pnid='{2}'", zsuserid, strPCID, strPNID);
                DataTable dt4 = null;
                Outida.RunSql(sql4, out dt4);
                if (dt4.Rows.Count > 0)
                {

                }
                else
                {
                    string sql3 = string.Format(@"select distinct 征收批次号,首次 from zs_zspch where pcid='{0}' and pnid ='{1}'", strPCID, strPNID);
                    DataTable dt3 = null;
                    Outida.RunSql(sql3, out dt3);
                    if (dt3.Rows.Count > 0)
                    {
                        string pch = dt3.Rows[0]["征收批次号"].ToString();
                        string sc = dt3.Rows[0]["首次"].ToString();
                        string ttID = System.Guid.NewGuid().ToString().Replace("-", "");
                        string sql2 = string.Format(@"insert into zs_zspch (id,pnid,征收批次号,pcid,zsuserid,首次) values ('{0}','{1}','{2}','{3}','{4}','{5}')", ttID, strPNID, pch, strPCID, zsuserid, sc);
                        Outida.RunSql(sql2);
                    }
                }
            }
        }
        public override Zspch GetZspch(string strid)
        {
            Zspch zspch = new Zspch();
            string sql = "select * from zs_zspch where id='" + strid + "' ";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return zspch = DataHelper.DataTableToTOne<Zspch>(dt);
        }
        public override bool SaveZspch(string strid, Zspch zspch)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(strid))
            {
                sql = DataHelper.AddT<Zspch>(zspch, "zs_zspch");
            }
            else
            {
                sql = DataHelper.UpdateT<Zspch>(zspch, "zs_zspch", new string[] { "id" });
            }
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override string GetGZJE(string personid, string strpnid, string type)
        {
            string sql = string.Empty;
            string temp = string.Empty;
            if (string.IsNullOrEmpty(type)) { type = "1"; }
            if (type == "1")
            {
                sql = string.Format(@"select to_number(nvl(replace(t1.textbox26,'\',''),0))+to_number(nvl(replace(t1.textbox27,'\',''),0))+
       to_number(nvl(replace(t1.textbox28,'\',''),0))+to_number(nvl(replace(t1.textbox28,'\',''),0))+
       to_number(nvl(replace(t1.textbox30,'\',''),0))+to_number(nvl(replace(t1.textbox31,'\',''),0))+
       to_number(nvl(replace(t1.textbox32,'\',''),0))+to_number(nvl(replace(t1.textbox33,'\',''),0))+
       to_number(nvl(replace(t1.textbox34,'\',''),0))+to_number(nvl(replace(t1.textbox35,'\',''),0))+
       to_number(nvl(replace(t1.textbox42,'\',''),0))+to_number(nvl(replace(t1.textbox43,'\',''),0))+
       to_number(nvl(replace(t1.textbox44,'\',''),0))+to_number(nvl(replace(t1.textbox45,'\',''),0))+
       to_number(nvl(replace(t1.textbox46,'\',''),0))+to_number(nvl(replace(t1.textbox166,'\',''),0)) 产权补助
 from zs_cqzhxy t1 where t1.fid in (
select id from zs_proloupan where zspid='{0}')", personid);
            }
            else if (type == "2")
            {
                sql = string.Format(@"select to_number(nvl(replace(t2.textbox24,'\',''),0))+to_number(nvl(replace(t2.textbox25,'\',''),0))+
       to_number(nvl(replace(t2.textbox26,'\',''),0))+to_number(nvl(replace(t2.textbox27,'\',''),0))+
       to_number(nvl(replace(t2.textbox28,'\',''),0))+to_number(nvl(replace(t2.textbox29,'\',''),0))+
       to_number(nvl(replace(t2.textbox30,'\',''),0))+to_number(nvl(replace(t2.textbox31,'\',''),0))+
       to_number(nvl(replace(t2.textbox32,'\',''),0))+to_number(nvl(replace(t2.textbox33,'\',''),0))+
       to_number(nvl(replace(t2.textbox34,'\',''),0))+to_number(nvl(replace(t2.textbox41,'\',''),0))+
       to_number(nvl(replace(t2.textbox42,'\',''),0))+to_number(nvl(replace(t2.textbox43,'\',''),0))+
       to_number(nvl(replace(t2.textbox44,'\',''),0))+to_number(nvl(replace(t2.textbox45,'\',''),0)) 货币补助  
 from zs_hbhxy t2 where t2.personid ='{0}'", personid);
            }
            else if (type == "3")
            {
                sql = string.Format(@"select sum(to_number(nvl(replace(ZZ补偿金额合计, '\', ''),0))+to_number(nvl(replace(YY评估补偿金额, '\', ''),0))
  +to_number(nvl(replace(FYY评估补偿金额, '\', ''),0))+to_number(nvl(replace(ZS室内装饰装修评估补偿金额合计, '\', ''),0))) 补偿总 
  from zs_zzbcjfenhu where zspid='{0}'", personid);
            }
            else if (type == "4")
            {
                sql = @"select  sum(to_number(nvl(replace(划转金额, '\', ''),0))) 划转金额 
from zs_zspch where pnid='" + strpnid + "' and zsuserid='" + personid + "'";
            }
            return temp = Outida.GetValue(sql);
        }
        public override DataTable GetBCJDRYXX(string pid, string strwhere)
        {
            string sql = string.Format(@"select * from (select t.id,t.提交状态,t.审批状态,
(select 征收许可证号 from zs_prono where id=t.pnid) 征收决定号,
(select 产权人 from zs_zsperson where id=t.personid) 产权人,
(select 承租人 from zs_zsperson where id =t.personid) 承租人,
(select 房屋坐落 from zs_zsperson where id=t.personid) 坐落,
(select 房屋建筑面积 from zs_zsperson where id=t.personid) 建筑面积 
 from ZS_BCJD t where t.pnid ='{0}') where 1=1 ", pid);
            if (!string.IsNullOrEmpty(strwhere)) { sql += strwhere; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetBCJDXZRY(string pnid, string strwhere)
        {
            string sql = string.Format(@"select rownum 序号,id,产权人,承租人,证件号,房屋坐落 from zs_zsperson  where id not in(
       (select personid from zs_hbhxy where pnid ='{0}') union 
 ( select zspid from zs_proloupan where status=1 and id in (
 select fid from zs_cqzhxy) and pid='{0}' and zspid is not null)) ", pnid);
            if (!string.IsNullOrEmpty(strwhere)) { sql += strwhere; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetXZBCJDRY(string pid, string values, string type)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(type)) { type = "0"; }
            if (type == "0")
            {
                sql = string.Format(@" select z.产权人, z.证件号
   from ZS_BCJD h, ZS_ZSPERSON z
  where h.personid = z.id
    and h.pnid in (select pid from zs_zzbcjfenhu)
    and h.PNID = '{0}' and z.id in ('{1}')", pid, values.Replace("|", "','"));
            }
            else if (type == "1")
            {
                sql = string.Format(@" select * from zs_zsperson where id not in( 
 (select personid from zs_hbhxy where pnid ='{0}') union 
 ( select zspid from zs_proloupan where status=1 and id in (
 select fid from zs_cqzhxy) and pid='{0}' and zspid is not null))", pid);
            }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override bool SaveBCJD(string strid, BCJD bcjd)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(strid))
            {
                sql = DataHelper.AddT<BCJD>(bcjd, "ZS_BCJD");
            }
            else
            {
                sql = DataHelper.UpdateT<BCJD>(bcjd, "ZS_BCJD", new string[] { "id" });
            }
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override DataTable GetYBBXX(string strwhere)
        {
            string sql = @"Select Id,Year,Month,qs,
(Case When pnid Is Not Null Then (Select 征收许可证号 From zs_prono Where Id =t.pnid) Else 征收决定号 End) 征收决定号,
(Case When pnid Is Not Null Then (Select 征收部门 From zs_project Where Id In (Select pid
 From zs_prono Where Id =t.pnid)) Else 征收部门 End) 征收部门,
(Case When pnid Is Not Null Then (Select 征收实施单位 From zs_project Where Id In (Select pid
 From zs_prono Where Id =t.pnid)) Else 征收实施单位 End) 征收实施单位,
(Case When pnid Is Not Null Then (Select 项目地址 From zs_project Where Id In (Select pid
 From zs_prono Where Id =t.pnid)) Else 项目地点 End) 项目地点,
(Case When pnid Is Not Null Then (Select 项目名称 From zs_project Where Id In (Select pid
 From zs_prono Where Id =t.pnid)) Else 项目名称 End) 项目名称,
(Case When pnid Is Not Null Then (Select to_char(权证户数) From zs_bcfaqs Where 补偿实施=0 and pId In (Select pid
 From zs_prono Where Id =t.pnid)) Else to_char(居民户数) End) 居民户数,
(Case When pnid Is Not Null Then (Select to_char(工企单位) From zs_bcfaqs Where 补偿实施=0 and pId In (Select pid
 From zs_prono Where Id =t.pnid)) Else to_char(工企单位) End) 工企单位,
(Case When pnid Is Not Null Then nvl((Select Round((to_number(nvl(房屋建筑面积,0))/10000),2) from zs_bcfaqs where 补偿实施=0 and pId In (Select pid
 From zs_prono Where Id =t.pnid)),0) Else 征收总面积 End) 面积,
to_char(征收期限1,'yyyy.MM.dd')||'-'||to_char(征收期限2,'yyyy.MM.dd') 征收签约期限
 From ZS_YBBTJ t where 1=1 ";
            if (!string.IsNullOrEmpty(strwhere)) { sql += strwhere; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetMyTable(string table, string column, string strwhere)
        {
            string sql = @"select " + column + " from " + table + " where 1=1 " + strwhere + "";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetCaseSF(string strwhere)
        {
            string sql = "select wynewpackage.GetItemTypeName('结案情况',t.jaqk) sjaqk,t.* from ZS_CASE_REGISTER t where 1=1 ";

            //以下限定为走流程的项目方可查看
            //string sql = "select wynewpackage.GetItemTypeName('结案情况',t.jaqk) sjaqk,t.* from ZS_CASE_REGISTER t where 1=1 and t.iid in (select iid from st_instance where status <> -2)";
            if (!string.IsNullOrEmpty(strwhere))
            {
                sql += strwhere;
            }
            sql += " order by t.slsj desc,t.caseid desc ";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetSFBasicInfo(string tableName, string selectKey, string whereValue)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(selectKey))
            {
                sql = "select rownum 序号,t.* from " + tableName + " t where 1 = 1 ";
            }
            else
            {
                sql = string.Format("select rownum 序号,{0} t.* from {1} t where 1 = 1 ", selectKey, tableName);
            }
            if (!string.IsNullOrEmpty(whereValue))
            {
                sql += whereValue;
            }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override CASE_REGISTER GetCASE_REGISTER(string strid)
        {
            CASE_REGISTER caseregister = new CASE_REGISTER();
            string sql = "select * from zs_CASE_REGISTER where id='" + strid + "' ";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return caseregister = DataHelper.DataTableToTOne<CASE_REGISTER>(dt);
        }
        public override bool SaveCaseRegister(string strid, CASE_REGISTER caseregister)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(strid))
            {
                sql = DataHelper.AddT<CASE_REGISTER>(caseregister, "ZS_CASE_REGISTER");
            }
            else
            {
                sql = DataHelper.UpdateT<CASE_REGISTER>(caseregister, "ZS_CASE_REGISTER", new string[] { "id" });
            }
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override QZZXXX GetQZZXXX(string strid)
        {
            QZZXXX qzzxxx = new QZZXXX();
            string sql = "select * from zs_qzzxxx where id='" + strid + "' ";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return qzzxxx = DataHelper.DataTableToTOne<QZZXXX>(dt);
        }
        public override bool SaveQZZXXX(string strid, QZZXXX qzzxxx)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(strid))
            {
                sql = DataHelper.AddT<QZZXXX>(qzzxxx, "ZS_QZZXXX");
            }
            else
            {
                sql = DataHelper.UpdateT<QZZXXX>(qzzxxx, "ZS_QZZXXX", new string[] { "id" });
            }
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override ONEJUSTICE GetONEJUSTICE(string strid)
        {
            ONEJUSTICE onejustice = new ONEJUSTICE();
            string sql = "select * from zs_onejustice where id='" + strid + "' ";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return onejustice = DataHelper.DataTableToTOne<ONEJUSTICE>(dt);
        }
        public override bool SaveONEJUSTICE(string strid, ONEJUSTICE onejustice)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(strid))
            {
                sql = DataHelper.AddT<ONEJUSTICE>(onejustice, "ZS_ONEJUSTICE");
            }
            else
            {
                sql = DataHelper.UpdateT<ONEJUSTICE>(onejustice, "ZS_ONEJUSTICE", new string[] { "id" });
            }
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override BCJDXZFY GetBCJDXZFY(string strid)
        {
            BCJDXZFY bcjdxzfy = new BCJDXZFY();
            string sql = "select * from zs_bcjdxzfy where id='" + strid + "' ";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return bcjdxzfy = DataHelper.DataTableToTOne<BCJDXZFY>(dt);
        }
        public override bool SaveBCJDXZFY(string strid, BCJDXZFY bcjdxzfy)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(strid))
            {
                sql = DataHelper.AddT<BCJDXZFY>(bcjdxzfy, "ZS_BCJDXZFY");
            }
            else
            {
                sql = DataHelper.UpdateT<BCJDXZFY>(bcjdxzfy, "ZS_BCJDXZFY", new string[] { "id" });
            }
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override DataTable GetXFXX(string type, string strwhere)
        {
            if (string.IsNullOrEmpty(type)) { type = "0"; }
            string sql = string.Empty;
            if (type == "0")
            {
                sql = @"Select Rownum 序号,
       P1.项目名称,
       P1.立项号,
       P1.区属,
       P1.征收实施单位,
    
       Z1.姓名,
       Z1.身份证号,
       Z1.信访时间,
       (Select Keyname
          From Zs_Params
         Where Name = '信访类别'
           And Keyvalue = Z1.信访类别) 信访类别,
       Z1.接待员,
       Z1.Id Zid,
       Z1.Pnid,
       P1.Id Pid
  From Zs_Project P1, Zs_Xinfang Z1
 Where Z1.Pnid = P1.Id
 " + strwhere;
            }
            else if (type == "1")
            {
                sql = @"Select Rownum 序号,
       j.区属,
       j.项目名称,
       j.立项号,
       j.征收实施单位,
       Z1.信访人姓名,
       Z1.信访时间,
       (Select Keyname
          From Zs_Params
         Where Name = '信访类别'
           And Keyvalue = Z1.信访类别) 信访类别,
       Z1.现住址,
       Z1.包案领导,
       Z1.Id Zid,
       Z1.Pnid
  From  Zs_Xinfangzd Z1,Zs_Xinfang xf, zs_project j
 Where 
    xf.id = Z1.Xfryid
    And xf.pnid = j.id
 " + strwhere;
            }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetHBHXYTJ(string strSJ1, string strSJ2, string strQS, string strFWYT,string userid)
        {
            string sqlWhere = string.Empty;
            if (!string.IsNullOrEmpty(strSJ1))
            {
                sqlWhere += " and t.UPDATETIMDE >= to_date('" + strSJ1 + "','yyyy-mm-dd')";
            }
            if (!string.IsNullOrEmpty(strSJ2))
            {
                sqlWhere += " and t.UPDATETIMDE < to_date('" + strSJ2 + "','yyyy-mm-dd')";
            }
            if (!string.IsNullOrEmpty(strQS))
            {
                string qsql = @"select keyname from zs_params where name='区属' and keyvalue='" + strQS + "'";
                string strQName = Outida.GetValue(qsql);
                sqlWhere += " and p1.区属='" + strQName + "'";
            }
            if (!string.IsNullOrEmpty(strFWYT))
            {
                sqlWhere += " and p2.id in(select z1.pid from zs_zzbcjfenhu z1,zs_zsperson z2 where z1.zspid=z2.id and z2.房屋用途='" + strFWYT + "')";
            }
            if (IsQZSBM(userid))
            {
                sqlWhere += @"and p1.区属 in(select keyname from zs_params where name='区属'and (Select tt.ROLENAME  From zs_USER_IN_ROLE t Join  zs_Role tt  On  t.roleid=tt.id
Where  t.userid Like '%"+userid+"%') Like '%'||keyname||'%')";
            }
            string sql = @"select distinct p1.项目名称,p1.立项号,
(select sum(to_number(nvl(replace(textbox73,'\','0'),0))) from zs_hbhxy where pnid in (select id from zs_prono where pid=p1.id)) 面积,
(select count(*) from zs_hbhxy where pnid in (select id from zs_prono where pid=p1.id)) 户数,
(select sum(to_number(nvl(replace(textbox19,'\','0'),0))) from zs_hbhxy where pnid in (select id from zs_prono where pid=p1.id)) 补偿,
(select sum(to_number(nvl(replace(textbox24,'\','0'),0))+to_number(nvl(replace(textbox25,'\','0'),0))+
to_number(nvl(replace(textbox26,'\','0'),0))+to_number(nvl(replace(textbox27,'\','0'),0))
+to_number(nvl(replace(textbox28,'\','0'),0))+to_number(nvl(replace(textbox29,'\','0'),0))+to_number(nvl(replace(textbox30,'\','0'),0))+to_number(nvl(replace(textbox31,'\','0'),0))
+to_number(nvl(replace(textbox32,'\','0'),0))+to_number(nvl(replace(textbox41,'\','0'),0))+to_number(nvl(replace(textbox42,'\','0'),0))
+to_number(nvl(replace(textbox43,'\','0'),0))+to_number(nvl(replace(textbox44,'\','0'),0))) from zs_hbhxy
where pnid in (select id from zs_prono where pid=p1.id)) 补助,
(select sum(to_number(nvl(replace(textbox34,'\','0'),0))) from zs_hbhxy where pnid in (select id from zs_prono where pid=p1.id)) 奖励,
(select sum(to_number(nvl(replace(textbox45,'\','0'),0))+to_number(nvl(replace(textbox33,'\','0'),0))) from zs_hbhxy where pnid=t.pnid) 其它
 from zs_hbhxy t,zs_project p1,zs_prono p2 where p1.id=p2.pid and t.pnid = p2.id  " + sqlWhere;
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetPGJGTJ(string type, string strwhere)
        {
            if (string.IsNullOrEmpty(type)) { type = "0"; }
            string sql = string.Empty;
            if (type == "0")
            {
                sql = @"Select P1.立项号,
       P1.项目名称,
       P1.建设单位,
       P1.区属,
       P1.征收部门,
       P1.征收实施单位,
       P1.概算评估机构,
       P1.Id 编号,
       P2.征收许可证号 征收决定号,
       To_Char(P2.拆迁开始日期, 'yyyy-mm-dd') 征收决定日期,
       P2.征收评估机构,
       Replace(B1.房屋建筑面积, '\', '') 房屋建筑面积,
       Replace(B1.房屋建筑面积住宅, '\', '') 房屋建筑面积住宅,
       Replace(B1.房屋建筑面积非住宅, '\', '') 房屋建筑面积非住宅,
       Replace(B1.权证户数, '\', '') 权证户数,
       Replace(B1.户口户数, '\', '') 户口户数,
       B1.工企单位,
       Replace(B1.征收补偿资金, '\', '') 征收补偿资金,
       B1.Id Bid,
       (Select Sum(To_Number(Nvl(Replace(Z1.Zz补偿金额合计, '\', ''), 0)) +
                   To_Number(Nvl(Replace(Z1.Yy评估补偿金额, '\', ''), 0)) +
                   To_Number(Nvl(Replace(Z1.Fyy评估补偿金额, '\', ''), 0)) +
                   To_Number(Nvl(Replace(Z1.Zs室内装饰装修评估补偿金额合计,
                                         '\',
                                         ''),
                                 0))) 总
          From Zs_Zzbcjfenhu Z1
         Where Z1.Pid = P2.Id) 概算补偿总金额
  From Zs_Project P1, Zs_Bcfaqs B1, Zs_Prono P2
 Where P1.Id = B1.Pid
   And P1.Id = P2.Pid
   And B1.补偿实施 = 0
   And P2.审核状态 = 1 ";
            }
            else if (type == "1")
            {
                sql = @"select distinct p1.立项号 from zs_project p1, zs_bcfaqs b1,zs_prono p2
         where p1.id = b1.pid and  p1.id=p2.pid 
           and b1.补偿实施 = 0 and p2.审核状态=1 ";
            }
            if (!string.IsNullOrEmpty(strwhere)) { sql += strwhere; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetPGZJTJ(string type, string strwhere)
        {
            if (string.IsNullOrEmpty(type)) { type = "0"; }
            string sql = string.Empty;
            if (type == "0")
            {
                sql = @"select p1.id 编号,p1.立项号,
       p1.项目名称,
       p1.建设单位,
       p1.区属,
       p1.征收部门,
       p1.征收实施单位,
       p1.概算评估机构,
       p3.*                
  from zs_project p1,zs_prono p2,zs_pggs p3 
 where p1.id = p2.pid and  p1.id=p3.pid and p1.审批状态=1 ";
            }
            else if (type == "1")
            {
                sql = @"select distinct p1.立项号 from zs_project p1,zs_prono p2,zs_pggs p3 
 where p1.id = p2.pid and  p1.id=p3.pid and p1.审批状态=1";
            }
            if (!string.IsNullOrEmpty(strwhere)) { sql += strwhere; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetXFHZ(string type, string strSJ1, string strSJ2, string strQS,string userid)
        {
            if (string.IsNullOrEmpty(type)) { type = "0"; }
            string sql = string.Empty;
            string sqlWhere = string.Empty;
            if (type == "0")
            {
                sql = @"select distinct zp.区属 from ZS_XINFANG zx 
left join (select distinct id, 区属 from zs_project) zp on zp.id in
 (select pid from zs_prono where id = zx.pnid) where 1=1 ";
                if (!string.IsNullOrEmpty(strSJ1))
                {
                    sqlWhere += " and zx.信访时间 >= to_date('" + strSJ1 + "','yyyy-mm-dd')";
                }
                if (!string.IsNullOrEmpty(strSJ2))
                {
                    sqlWhere += " and zx.信访时间 < to_date('" + strSJ2 + "','yyyy-mm-dd')";
                }
                if (!string.IsNullOrEmpty(strQS))
                {
                    sqlWhere += " and zx.pnid in (select id from zs_prono where pid in (select id from zs_project where 区属 ='" + strQS + "')) ";
                }
                if (IsQZSBM(userid))
                {
                    sqlWhere += @" and zx.pnid in (select id from zs_prono where pid in (select id from zs_project where 区属 in(select keyname from zs_params where name='区属'and (Select tt.ROLENAME  From zs_USER_IN_ROLE t Join  zs_Role tt  On  t.roleid=tt.id
Where  t.userid Like '%"+userid+"%') Like '%'||keyname||'%')))";
                }
                if (!string.IsNullOrEmpty(sqlWhere)) { sql += sqlWhere; }
            }
            else if (type == "1")
            {
                sql = @"select count(*) 人数 from zs_xinfang where pnid in (select id from zs_prono where pid in (
 select id from zs_project where 区属='" + strQS + "'))";
            }
           
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetXYTJ(string strSJ1, string strSJ2, string strQS, string strFWYT,string userid)
        {
            string sqlWhere = string.Empty;
            if (!string.IsNullOrEmpty(strSJ1))
            {
                sqlWhere += " and t.UPDATETIME >= to_date('" + strSJ1 + "','yyyy-mm-dd')";
            }
            if (!string.IsNullOrEmpty(strSJ2))
            {
                sqlWhere += " and t.UPDATETIME < to_date('" + strSJ2 + "','yyyy-mm-dd')";
            }
            if (!string.IsNullOrEmpty(strQS))
            {
                string qsql = @"select keyname from zs_params where name='区属' and keyvalue='" + strQS + "'";
                string strQName = Outida.GetValue(qsql);
//                sqlWhere += @" and t.fid in (select id from zs_proloupan where pid in (select id from zs_prono where pid in ( select id 
//from zs_project where 区属='" + strQName + "')))";
                sqlWhere += @" and t.fid in (select id from zs_proloupan where pid in (select pid from zs_prono where pid in ( select id 
from zs_project where 区属='" + strQName + "')))";
            }
            if (!string.IsNullOrEmpty(strFWYT))//暂未关联
            {
                sqlWhere += @" and t.fid in (select id from zs_proloupan where pid in (select z1.pid 
from zs_zzbcjfenhu z1,zs_zsperson z2 where z1.zspid=z2.id and z2.房屋用途='" + strFWYT + "'))";
            }
//            string sql = @"select distinct (select 项目名称 from zs_project where id in (select pid from zs_prono where id in (
//select pid from zs_proloupan where id=t.fid))) 项目名称,
//(select 立项号 from zs_project where id in (select pid from zs_prono where id in (
//select pid from zs_proloupan where id=t.fid))) 立项号,
//(select sum(to_number(nvl(replace(TextBox108,'\','0'),0))) from zs_cqzhxy where fid in (
//select id from zs_proloupan where pid in (select id from zs_prono where pid=p1.id))) 面积,
//(select count(*) from zs_cqzhxy where fid in (
//select id from zs_proloupan where pid in (select id from zs_prono where pid=p1.id))) 户数,
//(select sum(to_number(nvl(replace(textbox19,'\','0'),0))) from zs_cqzhxy where fid in (
//select id from zs_proloupan where pid in (select id from zs_prono where pid=p1.id))) 补偿,
//(select sum(to_number(nvl(replace(textbox26,'\','0'),0))+to_number(nvl(replace(textbox27,'\','0'),0))
//+to_number(nvl(replace(textbox28,'\','0'),0))+to_number(nvl(replace(textbox29,'\','0'),0))+to_number(nvl(replace(textbox30,'\','0'),0))+to_number(nvl(replace(textbox31,'\','0'),0))
//+to_number(nvl(replace(textbox32,'\','0'),0))+to_number(nvl(replace(textbox34,'\','0'),0))
//+to_number(nvl(replace(textbox42,'\','0'),0))+to_number(nvl(replace(textbox43,'\','0'),0))+to_number(nvl(replace(textbox44,'\','0'),0))
//+to_number(nvl(replace(textbox45,'\','0'),0))) from zs_cqzhxy where fid in (
//select id from zs_proloupan where pid in (select id from zs_prono where pid=p1.id))) 补助,
//(select sum(to_number(nvl(replace(textbox166,'\','0'),0))) from zs_cqzhxy where fid in (
//select id from zs_proloupan where pid in (select id from zs_prono where pid=p1.id))) 奖励,
//(select sum(to_number(nvl(replace(textbox35,'\','0'),0))+to_number(nvl(replace(textbox46,'\','0'),0))) 
//from zs_cqzhxy where fid in (
//select id from zs_proloupan where pid in (select id from zs_prono where pid=p1.id))) 其它
// from zs_cqzhxy t,zs_project p1,zs_prono p2,zs_proloupan p3 
//where p1.id=p2.pid and p3.pid = p2.id and t.fid=p3.id " + sqlWhere;
            if (IsQZSBM(userid))
            {
                sqlWhere += @"and p1.区属 in(select keyname from zs_params where name='区属'and (Select tt.ROLENAME  From zs_USER_IN_ROLE t Join  zs_Role tt  On  t.roleid=tt.id
Where  t.userid Like '%"+userid+"%') Like '%'||keyname||'%')";
            }
            string sql = @"
select distinct (select 项目名称
                   from zs_project
                  where id in
                        (select pid
                           from zs_prono
                          where pid in
                                (select pid from zs_proloupan where id = t.fid))) 项目名称,
                (select 立项号
                   from zs_project
                  where id in
                        (select pid
                           from zs_prono
                          where pid in
                                (select pid from zs_proloupan where id = t.fid))) 立项号,
                (select sum(to_number(nvl(replace(TextBox108, '\', '0'), 0)))
                   from zs_cqzhxy
                  where fid in
                        (select id
                           from zs_proloupan
                          where pid in
                                (select pid from zs_prono where pid = p1.id))) 面积,
                (select count(*)
                   from zs_cqzhxy
                  where fid in
                        (select id
                           from zs_proloupan
                          where pid in
                                (select pid from zs_prono where pid = p1.id))) 户数,
                (select sum(to_number(nvl(replace(textbox19, '\', '0'), 0)))
                   from zs_cqzhxy
                  where fid in
                        (select id
                           from zs_proloupan
                          where pid in
                                (select pid from zs_prono where pid = p1.id))) 补偿,
                (select sum(to_number(nvl(replace(textbox26, '\', '0'), 0)) +
                            to_number(nvl(replace(textbox27, '\', '0'), 0)) +
                            to_number(nvl(replace(textbox28, '\', '0'), 0)) +
                            to_number(nvl(replace(textbox29, '\', '0'), 0)) +
                            to_number(nvl(replace(textbox30, '\', '0'), 0)) +
                            to_number(nvl(replace(textbox31, '\', '0'), 0)) +
                            to_number(nvl(replace(textbox32, '\', '0'), 0)) +
                            to_number(nvl(replace(textbox34, '\', '0'), 0)) +
                            to_number(nvl(replace(textbox42, '\', '0'), 0)) +
                            to_number(nvl(replace(textbox43, '\', '0'), 0)) +
                            to_number(nvl(replace(textbox44, '\', '0'), 0)) +
                            to_number(nvl(replace(textbox45, '\', '0'), 0)))
                   from zs_cqzhxy
                  where fid in
                        (select id
                           from zs_proloupan
                          where pid in
                                (select pid from zs_prono where pid = p1.id))) 补助,
                (select sum(to_number(nvl(replace(textbox166, '\', '0'), 0)))
                   from zs_cqzhxy
                  where fid in
                        (select id
                           from zs_proloupan
                          where pid in
                                (select pid from zs_prono where pid = p1.id))) 奖励,
                (select sum(to_number(nvl(replace(textbox35, '\', '0'), 0)) +
                            to_number(nvl(replace(textbox46, '\', '0'), 0)))
                   from zs_cqzhxy
                  where fid in
                        (select id
                           from zs_proloupan
                          where pid in
                                (select pid from zs_prono where pid = p1.id))) 其它
  from zs_cqzhxy t, zs_project p1, zs_prono p2, zs_proloupan p3
 where p1.id = p2.pid
   and p3.pid = p2.pid
   and t.fid = p3.id
" + sqlWhere;
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetZSBCJHZ(bool rb1, bool rb2, string strSJ1, string strSJ2, string strQS, string strFWYT,string userid)
        {
            string sqlWhere = string.Empty;
            if (rb2)
            {
                sqlWhere += " and (to_number(nvl(replace(zp.到账金额,'\','0'), 0))+to_number(nvl(replace(zp.划转金额,'\','0'), 0))) < to_number(nvl(replace(zp.需划转额度,'\','0'), 0))";
            }
            if (rb1)
            {
                sqlWhere += " and to_number(nvl(replace(zp.到账金额,'\','0'), 0)) > to_number(nvl(replace(zp.划转金额,'\','0'), 0))";
            }
            if (!string.IsNullOrEmpty(strSJ1))
            {
                sqlWhere += " and zp.时间 >= to_date('" + strSJ1 + "','yyyy-mm-dd hh24:mi:ss')";
            }
            if (!string.IsNullOrEmpty(strSJ2))
            {
                sqlWhere += " and zp.时间 < to_date('" + strSJ2 + "','yyyy-mm-dd hh24:mi:ss')";
            }
            if (!string.IsNullOrEmpty(strQS))
            {
                string qsql = @"select keyname from zs_params where name='区属' and keyvalue='" + strQS + "'";
                string strQName = Outida.GetValue(qsql);
                sqlWhere += " and zp.区属='" + strQName + "'";
            }
            if (!string.IsNullOrEmpty(strFWYT))
            {
                sqlWhere += @" and zp.区属 in (select 区属 from zs_project where id in (select pid from zs_prono where id in 
(select z1.pid from zs_zzbcjfenhu z1,zs_zsperson z2 where z1.zspid=z2.id and z2.房屋用途='" + strFWYT + "')))";
            }
            sqlWhere += " and zp.时间 is not null";
            if (IsQZSBM(userid))
            {
                sqlWhere += @"  and zp.区属 in(select keyname from zs_params where name='区属'and (Select tt.ROLENAME  From zs_USER_IN_ROLE t Join  zs_Role tt  On  t.roleid=tt.id
Where  t.userid Like '%"+userid+"%') Like '%'||keyname||'%')";
            }
            string sql = @"select *
  from (select distinct p1.区属,
                        (select min(时间)
                           from zs_bcbasicinfo
                          where pnid in (select id
                                           from zs_project
                                          where 区属 = p1.区属 and id in (select pid from zs_prono where 审核状态=1))) 时间,
                        (select sum(replace(到账金额, '\', ''))
                           from zs_bcbasicinfo
                          where pnid in (select id
                                           from zs_project
                                          where 区属 = p1.区属 and id in (select pid from zs_prono where 审核状态=1))) 到账金额,
                        (select sum(replace(划转金额, '\', ''))
                           from zs_bcbasicinfo
                          where pnid in (select id
                                           from zs_project
                                          where 区属 = p1.区属 and id in (select pid from zs_prono where 审核状态=1))) 划转金额,
                        (select sum(replace(余额, '\', ''))
                           from zs_bcbasicinfo
                          where pnid in (select id
                                           from zs_project
                                          where 区属 = p1.区属 and id in (select pid from zs_prono where 审核状态=1))) 余额,
                        (select count(ID)
                           from zs_project
                          where 区属 = p1.区属 and id in (select pid from zs_prono where 审核状态=1)) 项目数,
                        (select sum(to_number(nvl(replace(ZZ承租人补偿款, '\', ''), 0)) +
                                    to_number(nvl(replace(ZZ产权人补偿款, '\', ''), 0)) +
                                    to_number(nvl(replace(YY承租人补偿款, '\', ''), 0)) +
                                    to_number(nvl(replace(YY产权人补偿款, '\', ''), 0)) +
                                    to_number(nvl(replace(FYY承租人补偿款, '\', ''), 0)) +
                                    to_number(nvl(replace(FYY产权人补偿款, '\', ''), 0)) +
                                    to_number(nvl(replace(ZS承租人补偿款, '\', ''), 0)) +
                                    to_number(nvl(replace(ZS产权人补偿款, '\', ''), 0)))
                           from zs_zzbcjfenhu
                          where pid in (select id from zs_prono where pid in (select id 
                                          from zs_project
                                         where 区属 = p1.区属 and id in (select pid from zs_prono where 审核状态=1)))) 需划转额度
          from zs_project p1) zp where 1 = 1 " + sqlWhere;
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetZSXMHZ(string strSJ1, string strSJ2, string strQS,string userid)
        {
            string sqlWhere = string.Empty;
            if (!string.IsNullOrEmpty(strSJ1))
            {
                sqlWhere += " and p1.addtime >= to_date('" + strSJ1 + "','yyyy-mm-dd')";
            }
            if (!string.IsNullOrEmpty(strSJ2))
            {
                sqlWhere += " and p1.addtime < to_date('" + strSJ2 + "','yyyy-mm-dd')";
            }
            if (!string.IsNullOrEmpty(strQS))
            {
                string qsql = @"select keyname from zs_params where name='区属' and keyvalue='" + strQS + "'";
                string strQName = Outida.GetValue(qsql);
                sqlWhere += " and p1.区属='" + strQName + "'";
            }
            if (IsQZSBM(userid))
            {
                sqlWhere += @"  and 区属 in(select keyname from zs_params where name='区属'and (Select tt.ROLENAME  From zs_USER_IN_ROLE t Join  zs_Role tt  On  t.roleid=tt.id
Where  t.userid Like '%"+userid+"%') Like '%'||keyname||'%')";
            }
            string sql = @"select distinct (p1.区属) 区属,
                (select count(ID) from zs_project where 区属 = p1.区属 and id in (
                select pid from zs_prono where 审核状态=1)) 项目数,
                to_number(nvl((select sum(replace(z2.房屋建筑面积, '\', ''))
                                from zs_zzbcjfenhu z1, zs_zsperson z2
                               where z1.zspid = z2.id
                                 and z1.pid in (select id from zs_prono where pid in (
                                 select id from zs_project where 区属=p1.区属) and 审核状态=1)),
                              0)) 面积,
                (select count(*)
                   from zs_zsperson
                  where 房屋权证编号 is not null
                    and 土地权证编号 is not null
                    and id in (select zspid
                                 from zs_zzbcjfenhu
                                where pid in (select id from zs_prono where pid in (
                                 select id from zs_project where 区属=p1.区属) and 审核状态=1))) 有证人数,
                (select count(*)
                   from zs_zsperson
                  where 房屋权证编号 is null
                    and 土地权证编号 is null
                    and id in (select zspid
                                 from zs_zzbcjfenhu
                                where pid in (select id from zs_prono where pid in (
                                 select id from zs_project where 区属=p1.区属) and 审核状态=1))) 无证人数,
                (select count(*)
                   from zs_cqzhxy zc
                  where fid in (select id
                                  from zs_proloupan
                                 where pid in (select id from zs_prono where pid in (
                                 select id from zs_project where 区属=p1.区属) and 审核状态=1))) 采用产权置换协议人数,
                (select count(*)
                   from zs_hbhxy
                  where pnid in (select id
                                   from zs_prono
                                  where pid in (select id
                                                  from zs_project
                                                 where 区属 = p1.区属) and 审核状态=1)) 采用货币化补偿协议人数,
                to_number(nvl((select sum(replace(国有土地面积, '\', '')) from zs_project where 区属=p1.区属 and id in (
                select pid from zs_prono where 审核状态=1)),0)) 国有土地面积,
                to_number(nvl((select sum(replace(集体土地面积, '\', '')) from zs_project where 区属=p1.区属 and id in (
                select pid from zs_prono where 审核状态=1)),0)) 集体土地面积
  from zs_project p1 where p1.id in (select pid from zs_prono where 审核状态=1) " + sqlWhere;
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override bool SaveYJCS(YJCS yjcs)
        {
            string sql = DataHelper.UpdateT<YJCS>(yjcs, "ZS_YJCS", new string[] { "id" });
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override bool SaveMenu(string strid, Menu menu)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(strid))
            {
                sql = DataHelper.AddT<Menu>(menu, "ZS_Menu");
            }
            else
            {
                sql = DataHelper.UpdateT<Menu>(menu, "ZS_Menu", new string[] { "id" });
            }
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override MESSAGE GetMESSAGE()
        {
            MESSAGE message = new MESSAGE();
            string sql = "select * from zs_message where 1=1 ";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return message = DataHelper.DataTableToTOne<MESSAGE>(dt);
        }
        public override DataTable GetROLE(string strwhere)
        {
            string sql = "select * from zs_role where 1=1 " + strwhere + "  order by addtime desc";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override bool SaveRole(string strid, Role role)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(strid))
            {
                sql = DataHelper.AddT<Role>(role, "ZS_Role");
            }
            else
            {
                sql = DataHelper.UpdateT<Role>(role, "ZS_Role", new string[] { "id" });
            }
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override DataTable SetRoleUser(string strid, string username)
        {
            string sql1 = "select t.*,(select Count(*) from zs_user_in_role tt Where roleid='" + strid + "' And userid=t.id) as isexist  from zs_user t where status=1 ";
            if (!string.IsNullOrEmpty(username))
            {
                sql1 += " and LOGINID like '%" + username + "%' or name like '%" + username + "%'";
            }
            string sql = "Select * From ( " + sql1 + "  ) Order By Isexist Desc";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override string GetRoleUser(string strid, string userid)
        {
            string sql = "select count(*)  from zs_user_in_role where  ROLEID='" + strid + "' and USERID='" + userid + "'";
            return Outida.GetValue(sql);
        }
        public override bool UpdateRoleUser(string type, string strid, string userid)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(type)) { return false; }
            else
            {
                if (type == "0")
                {
                    sql = "insert into zs_user_in_role(ROLEID,USERID)values('" + strid + "','" + userid + "')";
                }
                else if (type == "1")
                {
                    sql = "delete from zs_user_in_role where ROLEID='" + strid + "' and USERID='" + userid + "'";
                }
                return Outida.RunSql(sql) > 0 ? true : false;
            }
        }
        public override DataTable GetRoleMenu(string type, string strid, string menuid)
        {
            string sql = string.Empty;
            if (type == "0")
            {
                sql = "select t.* from zs_menu t where parent_id is null Order By parent_id, order_id Asc";
            }
            else if (type == "1")
            {
                sql = "select t.*,(select Count(*) from zs_MENU_IN_ROLE tt Where roleid='" + strid
                    + "' And MENUID=t.id) as isexist from zs_menu t where parent_id='" + menuid
                    + "' Order By parent_id, order_id Asc";
            }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override string SetRoleMenu(string strid, string menuid)
        {
            string sql = "select count(*)  from zs_MENU_IN_ROLE where  ROLEID='" + strid + "' and MENUID='" + menuid + "'";
            return Outida.GetValue(sql);
        }
        public override bool UpdateRoleMenu(string type, string strid, string menuid)
        {
            string sql = string.Empty;
            if (type == "0")
            {
                sql = "insert into zs_MENU_IN_ROLE(ROLEID,MENUID)values('" + strid + "','" + menuid + "')";
            }
            else if (type == "1")
            {
                sql = "delete from zs_MENU_IN_ROLE where ROLEID='" + strid + "' and MENUID='" + menuid + "'";
            }
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override DataTable GetUserAll(string strwhere)
        {
            string sql = "select * from zs_user where 1=1 " + strwhere + " order by addtime desc";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetXMName()
        {
            string sql = @"Select * From (Select Distinct ITEMID,
(Select 项目名称 From zs_project a Where a.Id=t.ITEMID)项目名称 From zs_file t) Where 项目名称 Is Not Null ";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetDTTable(string sql)
        {
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override string GetDTColumn(string sql)
        {
            return Outida.GetValue(sql);
        }
        public override DataTable GetDisParams()
        {
            string sql = "select Distinct Name from zs_params t";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override Params GetParamO(string strid)
        {
            Params _params = new Params();
            string sql = "select * from zs_params t where id='" + strid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return _params = DataHelper.DataTableToTOne<Params>(dt);
        }
        public override bool SaveParams(string strid, Params _params)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(strid))
            {
                sql = DataHelper.AddT<Params>(_params, "ZS_Params");
            }
            else
            {
                sql = DataHelper.UpdateT<Params>(_params, "ZS_Params", new string[] { "id" });
            }
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override DataTable GetCompany(string type, string strwhere)
        {
            string sql = @"select t.* from zs_company t where t.单位类别 = '" + type + "' ";
            if (!string.IsNullOrEmpty(strwhere)) { sql += strwhere; }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetSSComPer(string type, string cid, string strwhere)
        {
            string sql = string.Format(@"select t.*,(select 单位名称 from zs_company where id=t.qyid) qymc 
from ZS_PGJGRY t where t.rylb='{0}' and t.qyid='{1}' {2} ", type, cid, strwhere);
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetComDis(string column, string type)
        {
            string sql = @"select distinct " + column + " from zs_company where 单位类别='" + type + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override void ComCopy(string rwnd)
        {
            string oldYear = (Convert.ToInt32(rwnd) - 1).ToString();
            string csql = @"select * from zs_company where 单位类别='评估机构' and 入围年度='" + rwnd + "'";
            DataTable cdt = null;
            Outida.RunSql(csql, out cdt);
            if (cdt.Rows.Count > 0) { }
            else
            {
                string sql = @"select * from zs_company where 单位类别='评估机构' and 入围年度='" + oldYear + "'";
                DataTable dt = null;
                Outida.RunSql(sql, out dt);
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        string tt1 = dt.Rows[i]["单位名称"].ToString();
                        string tt2 = dt.Rows[i]["单位类别"].ToString();
                        string tt3 = dt.Rows[i]["法定代表人"].ToString();
                        string tt4 = dt.Rows[i]["信用等级"].ToString();
                        //string tt5 = dt.Rows[i]["入围年度"].ToString();
                        string tt6 = dt.Rows[i]["法定代表人联系电话"].ToString();
                        string tt7 = dt.Rows[i]["房地产估价机构资质等级"].ToString();
                        string tt8 = dt.Rows[i]["有效期限"].ToString();
                        string tt9 = dt.Rows[i]["拆迁评估技术负责人"].ToString();
                        string tt10 = dt.Rows[i]["拆迁评估技术负责人联系电话"].ToString();
                        string tt11 = dt.Rows[i]["土地评估资质"].ToString();
                        string tt12 = dt.Rows[i]["联系人"].ToString();
                        string tt13 = dt.Rows[i]["传真"].ToString();
                        string tt14 = dt.Rows[i]["地址"].ToString();
                        string tt15 = dt.Rows[i]["联系电话"].ToString();
                        string tt16 = dt.Rows[i]["信用记录"].ToString();
                        string strID = dt.Rows[i]["ID"].ToString();
                        string newID = System.Guid.NewGuid().ToString().Replace("-", "").ToUpper();
                        string insql = string.Format(@"insert into zs_company (ID,单位名称,单位类别,法定代表人,信用等级,入围年度,法定代表人联系电话,
房地产估价机构资质等级,有效期限,拆迁评估技术负责人,拆迁评估技术负责人联系电话,土地评估资质,联系人,传真,地址,联系电话,信用记录) values 
('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}')", newID,
    tt1, tt2, tt3, tt4, rwnd, tt6, tt7, tt8, tt9, tt10, tt11, tt12, tt13, tt14, tt15, tt16);
                        Outida.RunSql(insql);
                        string sql1 = @"select * from zs_pgjgry where qyid='" + strID + "'";
                        DataTable dt1 = null;
                        Outida.RunSql(sql1, out dt1);
                        if (dt1.Rows.Count > 0)
                        {
                            for (int j = 0; j < dt1.Rows.Count; j++)
                            {
                                string ttt1 = dt1.Rows[j]["QYID"].ToString();
                                string ttt2 = dt1.Rows[j]["NAME"].ToString();
                                string ttt3 = dt1.Rows[j]["ZYBH"].ToString();
                                string ttt4 = dt1.Rows[j]["SFZH"].ToString();
                                string ttt5 = dt1.Rows[j]["GZZKSSJ"].ToString();

                                string ttt6 = dt1.Rows[j]["GZZJSSJ"].ToString();
                                string ttt7 = dt1.Rows[j]["BIRTHDAY"].ToString();
                                string ttt8 = dt1.Rows[j]["ZYZCHZG"].ToString();
                                string ttt9 = dt1.Rows[j]["CYSJ"].ToString();
                                string ttt10 = dt1.Rows[j]["LXDH"].ToString();

                                string ttt11 = dt1.Rows[j]["NOTES"].ToString();
                                string ttt12 = dt1.Rows[j]["PROJECT"].ToString();
                                string ttt13 = dt1.Rows[j]["KHCJ"].ToString();
                                string ttt14 = dt1.Rows[j]["KHSJ"].ToString();
                                string ttt15 = dt1.Rows[j]["RYLB"].ToString();

                                string ttt16 = dt1.Rows[j]["SEX"].ToString();
                                string ttt17 = dt1.Rows[j]["GZZXZ"].ToString();
                                string ttt18 = dt1.Rows[j]["ZZMM"].ToString();
                                string ttt19 = dt1.Rows[j]["SSQS"].ToString();
                                string ttt20 = dt1.Rows[j]["SZGW"].ToString();

                                string ttt21 = dt1.Rows[j]["WHCD"].ToString();
                                string ttt22 = dt1.Rows[j]["SHJZ"].ToString();
                                string ttt23 = dt1.Rows[j]["ZC"].ToString();
                                string insql1 = string.Format(@"insert into zs_pgjgry (QYID,NAME,ZYBH,SFZH,GZZKSSJ,
GZZJSSJ,BIRTHDAY,ZYZCHZG,CYSJ,LXDH,
NOTES,PROJECT,KHCJ,KHSJ,RYLB,
SEX,GZZXZ,ZZMM,SSQS,SZGW,
WHCD,SHJZ,ZC) values ('{0}','{1}','{2}','{3}',to_date('{4}','yyyy-mm-dd hh24-mi-ss'),
to_date('{5}','yyyy-mm-dd hh24-mi-ss'),to_date('{6}','yyyy-mm-dd hh24-mi-ss'),'{7}',to_date('{8}','yyyy-mm-dd hh24-mi-ss'),'{9}',
'{10}','{11}','{12}',to_date('{13}','yyyy-mm-dd hh24-mi-ss'),'{14}',
'{15}','{16}','{17}','{18}','{19}',
'{20}','{21}','{22}')", newID, ttt2, ttt3, ttt4, ttt5, ttt6, ttt7, ttt8, ttt9, ttt10, ttt11, ttt12, ttt13, ttt14, ttt15, ttt16, ttt17, ttt18,
                      ttt19, ttt20, ttt21, ttt22, ttt23);
                                Outida.RunSql(insql1);
                            }
                        }
                    }
                }
            }
        }
        public override DataTable GetZSBM()
        {
            string sql = @"Select '' id,'' rolename From dual Union All
Select t.id,t.rolename From zs_role t Where rolename Like '%征收部门'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetYHSQ(string userid)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(userid))
            {
                sql = @"Select a.*,
(
Select Count(*) From zs_Menu_In_User Where Userid = a.id
And menuid Not In (Select menuid From zs_menu_in_role Where  roleid In
(Select roleid From zs_User_In_Role Where userid=a.id))

) As cbnum
 From zs_User a
 order by ADDTIME Desc";
            }
            else
            {
                sql = @" Select a.*,
(
Select Count(*) From zs_Menu_In_User Where Userid = a.id
And menuid Not In (Select menuid From zs_menu_in_role Where  roleid In
(Select roleid From zs_User_In_Role Where userid=a.id))

) As cbnum
From zs_User a Where Id In (

Select userid From zs_user_company t,zs_user tt Where  t.userid=tt.id And t.cid In
(Select cid From zs_user_company t Where t.userid='" + userid + @"')
And tt.iscomuser<>0

)  order by ADDTIME desc";
            }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable SetUserMenuQ(string type, string userid, string parentid, string strwhere)
        {
            string sql = string.Empty;
            if (type == "0")
            {
                sql = "select t.* from zs_menu t where parent_id is null Order By order_id Asc";
            }
            else if (type == "1")
            {
                sql = @"Select * From zs_Menu Where Id In (
Select distinct parent_id From zs_Menu Where 
Id In (select distinct menuid from (Select menuid From zs_menu_in_user 
Where userid ='" + userid + @"')
Union
Select menuid From zs_Menu_In_Role Where roleid In(
Select roleid From zs_User_In_Role Where userid='" + userid + @"')
)) order by ORDER_ID asc";
            }
            else if (type == "2")
            {
                sql = @"  select t.*,(select Count(*) from zs_menu_in_user tt
       Where USERID='" + userid + @"' And MENUID=t.id) as isexist ,
       (select Count(*) from zs_MENU_IN_ROLE tt Where roleid in
       (SELECT ROLEID FROM zs_user_in_role WHERE USERID='" + userid + @"')
       And MENUID=t.id) as isexist2
       from zs_menu t where parent_id='" + parentid + @"' 
       " + strwhere + @" Order By parent_id, order_id Asc";
            }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override string GetUserMenuQ(string userid, string menuid)
        {
            string sql = "select count(*)  from zs_menu_in_user where  USERID='" + userid + "' and MENUID='" + menuid + "'";
            return Outida.GetValue(sql);
        }
        public override bool UpdateUserMenuQ(string type, string userid, string menuid)
        {
            string sql = string.Empty;
            if (type == "0")
            {
                sql = "insert into zs_menu_in_user(USERID,MENUID)values('" + userid + "','" + menuid + "')";
            }
            else if (type == "1")
            {
                sql = "delete from zs_menu_in_user where USERID='" + userid + "' and MENUID='" + menuid + "'";
            }
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override DataTable GetGongGao()
        {
            string sql = @"select t.*,(Select Name From zs_User Where Id=t.userid) username from zs_gonggao t Order By addtime Desc";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetRiZhi(string type, string strwhere)
        {
            string sql = string.Empty;
            if (type == "0")
            {
                sql = "Select Distinct EVENTNAME From zs_rizhi  ";
            }
            else if (type == "1")
            {
                sql = @"select id, (Select Name From zs_User Where Id=eventuser) As 用户,
'' As 对象,
EVENTNAMe 名称 ,
EVENTDATE 时间,
t.rowid from zs_rizhi t where 1=1 " + strwhere + " order by eventdate desc";
            }
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override void FSZNMessage(string luserid, string ltime, string fsbt, string fsnr, string pgjg)
        {
            string[] tt = pgjg.Split(',');
            if (tt.Length > 0)
            {
                for (int i = 0; i < tt.Length; i++)
                {
                    string t1 = tt[i];
                    string sql = @"select userid from zs_user_company where cid in (select id from zs_company where 单位名称 ='" + t1 + "')";
                    DataTable dt = null;
                    Outida.RunSql(sql, out dt);
                    if (dt.Rows.Count > 0)
                    {
                        for (int j = 0; j < dt.Rows.Count; j++)
                        {
                            string strUid = dt.Rows[j]["userid"].ToString();
                            string sql1 = string.Format(@"insert into ZS_MESSAGE (SENDUSERID,RECEIVEUSERID,SENDTIME,MESSAGETITLE,MESSAGECONTENT) 
                        values ('{0}','{1}',to_date('{2}','yyyy-MM-dd hh24-mi-ss'),'{3}','{4}')", luserid, strUid, ltime, fsbt, fsnr);
                            Outida.RunSql(sql1);
                        }
                    }
                }
            }
        }
        public override DataTable GetZNMessage(string strwhere)
        {
            string sql = @"select * from ZS_MESSAGE where 1=1 " + strwhere + "";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override DataTable GetFileControl(string itemid)
        {
            string sql = "select t.*,(Select 项目名称 From zs_project a Where a.Id=t.ITEMID) As 项目名称 from zs_file t where ITEMID='" + itemid + "' Order By itemid,itemname";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return dt;
        }
        public override bool ckuserinrole(string strcid)
        {
            if (!string.IsNullOrEmpty(strcid))
            {
                string sql = @"select * from zs_user_in_role where userid in (
select userid from zs_user_company where cid='" + strcid + "')";
                DataTable dt = null;
                Outida.RunSql(sql, out dt);
                if (dt.Rows.Count > 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            else
            {
                return false;
            }
        }
        public override void checkuserrole(string type, string type1, string strcid, string cname, string roleid)
        {
            string sql = "Select Id From zs_user Where Name='" + cname + "'";
            string userhave = Outida.GetValue(sql);
            string userid = "";
            if (string.IsNullOrEmpty(userhave))
            {
                sql = string.Format(@"Select (Case When max(to_number(replace(LOGINID,'{0}',''))) Is Null Then 0
Else max(to_number(replace(LOGINID,'{0}',''))) End) num
From zs_user Where LOGINID Like '{0}%'", type);
                int zsbmhave = Convert.ToInt32(Outida.GetValue(sql));
                int snum = 1;
                string username = type + "000" + snum;
                if (zsbmhave > 0)
                {
                    username = type + "000" + (zsbmhave + 1);
                }
                userid = Guid.NewGuid().ToString().Replace("-", "");
                sql = "Insert Into zs_user(id,loginid,Name,pwd,ismodname)Values('" + userid + "','" + username + "','" + cname + "','" + username + "','0')";
                Outida.RunSql(sql);
                if (type == "PG")
                {
                    sql = @"Insert into zs_user_company (userid,cid) values ('" + userid + "','" + strcid + "')";
                    Outida.RunSql(sql);
                }
            }
            else
            {
                userid = userhave.ToString();
            }
            sql = @"Select Count(*) From zs_user_in_role Where  roleid In(
Select id From zs_role Where rolename Like '%" + type1 + "') And userid='" + userid + "'";
            int rolehave = Convert.ToInt32(Outida.GetValue(sql));
            if (rolehave == 0)
            {
                if (type == "PG")
                {
                    sql = "Insert Into zs_user_in_role(id,userid,roleid)Values(sys_guid(),'" + userid + "','C5909978A71420D8E043AC37373720D8')";
                    Outida.RunSql(sql);
                    string sql1 = "Insert Into zs_user_in_role(id,userid,roleid)Values(sys_guid(),'" + userid + "','C74B21C508ED80AEE043AC37373780AE')";
                    Outida.RunSql(sql1);
                }
                else if (type == "ZS")
                {
                    sql = "Insert Into zs_user_in_role(id,userid,roleid)Values(sys_guid(),'" + userid + "','" + roleid + "')";
                    Outida.RunSql(sql);
                }
            }
        }
        public override string CkComType(string userid)
        {
            string sql = @"select 单位类别 from zs_company where id in (
select cid from zs_user_company where userid='" + userid + "')";
            return Outida.GetValue(sql);
        }
        public override bool CkFileCount(string itemid, string usetype)
        {
            string sql = "select count(*) from zs_file where itemid='" + itemid + "' and use_type='" + usetype + "'";
            int tt = Convert.ToInt32(Outida.GetValue(sql));
            if (tt == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        public override bool UpdateFileCS(string itemid, string usetype)
        {
            string sql = "update zs_file set CAN_SYNC='1' where itemid='" + itemid + "' and USE_TYPE='" + usetype + "'";
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override PGBMJL GetPgbmjl(string strid)
        {
            PGBMJL pgbmjl = new PGBMJL();
            string sql = "select * from ZS_PGBMJL where id='" + strid + "'";
            DataTable dt = null;
            Outida.RunSql(sql, out dt);
            return pgbmjl = DataHelper.DataTableToTOne<PGBMJL>(dt);
        }
        public static string GetIID(string ownerid)
        {
            //string ZSConnectionString = base.ProviderProperties["ZSConnectionString"].ToString();
            //IDataAccess ida = DataFactory.GetInstance(false, DatabaseType.Oracle, ZSConnectionString);
            string strIId = string.Empty;
            //string sql = "select IID from xt_instance_ext where 关联ID='" + ownerid + "'";
            //strIId = ida.GetValue(sql);
            return strIId;
        }
        public override string GetDHZKX(string pnid)
        {
            string temp = string.Empty;
            string sql = string.Format(@"select ((to_number(nvl((select sum(to_number(nvl(replace(TextBox19,'\',''), 0)))
                         from zs_cqzhxy
                        where fid in
                              (select id
                                 from zs_proloupan
                                where pid in
                                      (select pid
                                         from zs_prono
                                        where id = '{0}'))),
                       0)) +
       to_number(nvl((select sum(to_number(nvl(replace(TextBox121,'\',''), 0)))
                         from zs_hbhxy
                        where pnid = '{0}'),
                       0))) +
       (to_number(nvl((Select sum(to_number(nvl(replace(t1.textbox26,'\',''), 0)) +
                                  to_number(nvl(replace(t1.textbox27,'\',''), 0)) +
                                  to_number(nvl(replace(t1.textbox28,'\',''), 0)) +
                                  to_number(nvl(replace(t1.textbox28,'\',''), 0)) +
                                  to_number(nvl(replace(t1.textbox30,'\',''), 0)) +
                                  to_number(nvl(replace(t1.textbox31,'\',''), 0)) +
                                  to_number(nvl(replace(t1.textbox32,'\',''), 0)) +
                                  to_number(nvl(replace(t1.textbox33,'\',''), 0)) +
                                  to_number(nvl(replace(t1.textbox34,'\',''), 0)) +
                                  to_number(nvl(replace(t1.textbox35,'\',''), 0)) +
                                  to_number(nvl(replace(t1.textbox42,'\',''), 0)) +
                                  to_number(nvl(replace(t1.textbox43,'\',''), 0)) +
                                  to_number(nvl(replace(t1.textbox44,'\',''), 0)) +
                                  to_number(nvl(replace(t1.textbox45,'\',''), 0)) +
                                  to_number(nvl(replace(t1.textbox46,'\',''), 0)) +
                                  to_number(nvl(replace(t1.textbox166,'\',''), 0)))
                         from zs_cqzhxy t1
                        where t1.fid in
                              (select id
                                 from zs_proloupan
                                where pid in
                                      (select pid
                                         from zs_prono
                                        where id =
                                              '{0}'))),
                       0)) +
       
       to_number(nvl((select sum(to_number(nvl(replace(t2.textbox24,'\',''), 0)) +
                                  to_number(nvl(replace(t2.textbox25,'\',''), 0)) +
                                  to_number(nvl(replace(t2.textbox26,'\',''), 0)) +
                                  to_number(nvl(replace(t2.textbox27,'\',''), 0)) +
                                  to_number(nvl(replace(t2.textbox28,'\',''), 0)) +
                                  to_number(nvl(replace(t2.textbox29,'\',''), 0)) +
                                  to_number(nvl(replace(t2.textbox30,'\',''), 0)) +
                                  to_number(nvl(replace(t2.textbox31,'\',''), 0)) +
                                  to_number(nvl(replace(t2.textbox32,'\',''), 0)) +
                                  to_number(nvl(replace(t2.textbox33,'\',''), 0)) +
                                  to_number(nvl(replace(t2.textbox34,'\',''), 0)) +
                                  to_number(nvl(replace(t2.textbox41,'\',''), 0)) +
                                  to_number(nvl(replace(t2.textbox42,'\',''), 0)) +
                                  to_number(nvl(replace(t2.textbox43,'\',''), 0)) +
                                  to_number(nvl(replace(t2.textbox44,'\',''), 0)) +
                                  to_number(nvl(replace(t2.textbox45,'\',''), 0)))
                         from zs_hbhxy t2
                        where t2.pnid = '{0}'),
                       0))) -
       (select sum(to_number(nvl(replace(划转金额,'\',''), 0))) 划转金额
           from zs_zspch
          where pnid = '{0}')) from dual", pnid);
            temp = Outida.GetValue(sql);
            return temp;
        }

        public override bool DeleteSome(string table, string strwhere)
        {
            string sql = "delete from " + table + " where 1=1 " + strwhere + "";
            return Outida.RunSql(sql) > 0 ? true : false;
        }
        public override string GetOneStr(string table, string ncol, string col, string value)
        {
            string sql = @"select " + ncol + " from " + table + " where " + col + "='" + value + "'";
            return Outida.GetValue(sql);
        }
        public override void InsertSome(string table, string col, string val)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(val))
            {
                sql = @"insert into " + table + " (" + col + ") ";
            }
            else
            {
                sql = @"insert into " + table + " (" + col + ") values (" + val + ")";
            }
            Outida.RunSql(sql);
        }
        public override void UpdateFileContent(string tablename, string colname, string id, byte[] FileContent)
        {
            try
            {
                OracleConnection oc = new OracleConnection();
                oc.ConnectionString = ZSConnectionString;
                OracleCommand cmd = new OracleCommand("update " + tablename + " set " + colname + "=:bFileContent  where id='" + id + "'", oc);
                OracleParameter op1 = new OracleParameter("bFileContent", OracleType.Blob, FileContent.Length);
                op1.Value = FileContent;
                cmd.Parameters.Add(op1);
                oc.Open();
                cmd.ExecuteNonQuery();
                oc.Close();
            }
            catch
            {

            }
        }
        public override string GetSomeStr(string table, string col, string strwhere)
        {
            string sql = "select  " + col + " from " + table + " where 1=1 " + strwhere + "";
            return Outida.GetValue(sql);
        }
        public override void UpdateSome(string table, string colval, string strwhere)
        {
            string sql = @"update " + table + " set " + colval + " where 1=1 " + strwhere + "";
            Outida.RunSql(sql);
        }
        public override DataSet GetOneDs(string table, string col, string strwhere)
        {
            DataSet ds = new DataSet();
            string sql = @"select " + col + " from " + table + " where 1=1 " + strwhere + "";
            Outida.RunSql(sql, out ds);
            return ds;
        }
        public override void RunSqlNon(string sql)
        {
            Outida.RunSql(sql);
        }

        //(select s.cqssdw  from cq_item s where s.XKZH = a.xkzhnew)
        /// <summary>
        /// 统计
        /// </summary>
        /// <param name="whereValue">除第三项之外的所有过滤条件</param>
        /// <param name="fsqk">发送情况 如果是全部（就传递空“”，否则传递发送情况的类别的index）</param>
        /// <param name="stringDate">受理时间与结转时间过滤条件</param>
        /// <returns></returns>
        public override DataTable GetCjsjStat(string whereValue, string fsqk, string stringDate)
        {
            string strSql = string.Format(@"Select (Select Distinct Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id)))) 许可证号,
       (Select Distinct 单位名称
          From Zs_Company
         Where 单位类别 = '征收实施单位'
           And 所属区属 In (Select s.Keyname
                         From Zs_Params s
                        Where s.Name = '区属'
                          And s.Keyvalue In (Select Szq From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id)))))) 单位名称,
       t.项目名称,
       (Select Count(*) 征收户数
          From Zs_Zzbcjfenhu
         Where Pid In (Select Id From zs_prono Where pid=t.id)) 征收户数,
       (Select Count(*)
          From Zs_Case_Register
         Where Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))
           And Jzsj Is Not Null) 按转户数,
       (Select Count(*)
          From Zs_Case_Register
         Where Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))
           And Jzsj Is Null) 申请户数,
       (Select Count(*)
          From Zs_Case_Register
         Where Jaqk = 1
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 正在审理,
       (Select Count(*)
          From Zs_Case_Register
         Where Jaqk = 2
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 调解解决,
       (Select Count(*)
          From Zs_Case_Register
         Where Jaqk = 4
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 不予受理,
       (Select Count(*)
          From Zs_Case_Register
         Where (Jaqk = 3)
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 已完成户数,
       (Select Count(*)
          From Zs_Case_Register
         Where (Jaqk = 5)
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 中止户数,
       (Select Count(*)
          From Zs_Case_Register
         Where (Jaqk = 6)
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 终结户数,
       (Select Count(*)
          From Zs_Case_Register
         Where Cuigaofachudate Is Not Null
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 催告已发出,
       (Select Count(*)
          From Zs_Case_Register
         Where Cuigaosongdadate Is Not Null
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 催告已送达,
       
       (Select Count(*)
          From Zs_Case_Register
         Where Qiangzhidate Is Not Null
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 已提交法院户数,
       
       (Select Count(*)
          From Zs_Case_Register
         Where Tingzhengdate Is Not Null
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 已听证户数,
       
       (Select Count(*)
          From Zs_Case_Register
         Where Fysljg = 1
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 准予申请户数,
       
       (Select Count(*)
          From Zs_Case_Register
         Where Fysljg = 2
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 准予撤回申请户数,
       
       (Select Count(*)
          From Zs_Case_Register
         Where Fysljg = 3
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 不予申请户数,
       (Select Count(*)
          From Zs_Case_Register
         Where Fyzxqk = 1
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 已调节,
       
       (Select Count(*)
          From Zs_Case_Register
         Where Fyzxqk = 2
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 已强制,
       (Select Count(*)
          From Zs_Case_Register
         Where Xyqiangzhidate Is Not Null
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 先予已提交法院户数,
       
       (Select Count(*)
          From Zs_Case_Register
         Where Xytingzhengdate Is Not Null
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 先予已听证户数,
       
       (Select Count(*)
          From Zs_Case_Register
         Where Xyfysljg = 1
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 先予准予申请户数,
       
       (Select Count(*)
          From Zs_Case_Register
         Where Xyfysljg = 2
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 先予准予撤回申请户数,
       
       (Select Count(*)
          From Zs_Case_Register
         Where Xyfysljg = 3
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 先予不予申请户数,
       (Select Count(*)
          From Zs_Case_Register
         Where Xyfyzxqk = 1
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 先予已调节,
       
       (Select Count(*)
          From Zs_Case_Register
         Where Xyfyzxqk = 2
           And Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 先予已强制

  From ZS_PROJECT t Where id In (Select pid From zs_prono Where Id In (Select pid From zs_zzbcjfenhu Where Id In 
  (Select 关联id From xt_instance_ext Where iid In (select iid from st_instance where status <>-2) 
And iid In (Select iid from zs_case_register a where 1=1 {0} {1})))) ", whereValue, stringDate);
            DataTable dt = new DataTable();

            Outida.RunSql(strSql, out dt);
            return dt;
        }


        #region 拆迁项目裁决受理情况表
        public override DataTable GetCjslInfoTable(string whereValue, string fsqk, string stringDate)
        {
            string strSql = string.Format(@"Select (Select Distinct Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id)))) 许可证号,
       t.项目名称,
       t.建设单位,
       t.项目地址,
       (Select Distinct 单位名称
          From Zs_Company
         Where 单位类别 = '征收实施单位'
           And 所属区属 In (Select s.Keyname
                         From Zs_Params s
                        Where s.Name = '区属'
                          And s.Keyvalue In (Select Szq From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id)))))) 单位名称,
       (Select Min(Slsj) From Zs_Case_Register Where Xkzh In (Select Xkzh From Zs_Case_Register Where iid In (
Select iid From xt_instance_ext Where 关联ID In (
Select Id From zs_zzbcjfenhu Where pid In (
Select Id From Zs_Prono Where pid=t.id))))) 最小时间
  From Zs_Project t 
  Where id In (Select pid From zs_prono Where Id In (Select pid From zs_zzbcjfenhu Where Id In 
  (Select 关联id From xt_instance_ext Where iid In (select iid from st_instance where status <>-2) 
And iid In (Select iid from zs_case_register a where 1=1 {0} {1})))) ", whereValue, stringDate);
            DataTable dt = new DataTable();
            Outida.RunSql(strSql, out dt);
            return dt;
        }
        #endregion

        public override string GetString(string sql)
        {
            return Outida.GetValue(sql);
        }

        public override DataTable GetTable(string sql)
        {
            DataTable dt = new DataTable();
            Outida.RunSql(sql, out dt);
            return dt;
        }

        #region  返回模糊查询中的案件号
        public override string GetCaseIdLike(string txt)
        {
            string str = string.Empty;
            if (txt.Trim().Length == 8)
            {
                int i;
                if (int.TryParse(txt.Trim(), out i))
                {
                    string nian = txt.Trim().Substring(0, 4);
                    string yue = txt.Trim().Substring(4, 4);
                    str = nian + "）第" + yue;
                }
                else
                {
                    str = txt;
                }
            }
            else if (txt.Trim().Length > 4 && txt.Trim().Length < 8)
            {
                int i;
                if (int.TryParse(txt.Trim(), out i))
                {
                    string nian = txt.Trim().Substring(0, 4);
                    string yue = txt.Trim().Substring(4, (txt.Trim().Length - 4));
                    str = nian + "）第" + yue;
                }
                else
                {
                    str = txt;
                }
            }
            else
            {
                str = txt;
            }
            return str;
        }
        #endregion

        public override string GetStringNeto2(string sql)
        {
            return Neto2ida.GetValue(sql);
        }
        public override string RunSqlNeto2(string sql)
        {
            return Neto2ida.GetValue(sql);
        }
        public override DataTable GetTableNeto2(string sql)
        {
            DataTable dt = new DataTable();
            Neto2ida.RunSql(sql, out dt);
            return dt;
        }
        public override void UpdatePlFileContent(string tablename, string colname, string id, byte[] FileContent)
        {
            try
            {
                OracleConnection oc = new OracleConnection();
                oc.ConnectionString = ZSConnectionString;
                OracleCommand cmd = new OracleCommand("update " + tablename + " set FILECONTENT=:bFileContent  where id='" + id + "'", oc);
                OracleParameter op1 = new OracleParameter("bFileContent", OracleType.Blob, FileContent.Length);
                op1.Value = FileContent;
                cmd.Parameters.Add(op1);
                oc.Open();
                cmd.ExecuteNonQuery();
                oc.Close();
            }
            catch
            {

            }
        }
    }
}
